Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2011
    Posts
    4

    Unanswered: Help needed with date compare

    Hi folks,

    I'm baffled with this one. I am selecting records based on a date comparison using the ISO standard yyyy-mm-dd . I'm getting a date is not in the acceptable range error only for the second date line. If I remove it, the query runs fine. This only occurs for certain records - when I examine them , they look fine. Any help would be appreciated.

    select a.acct#
    from ph#file.phpacct a
    where a.amdrc = '33-86-09'
    and date(a.aamcy || '-' || a.aammm || '-' || a.aamdd) <= '2010-01-03'
    and date(a.adscy || '-' || a.adsmm || '-' || a.adsdd) >= '2011-01-03'

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Why don't you store dates as DATE values instead of separating them into 3 columns?

    Can you show us he rows for which the comparison fails? Maybe you have an invalid month/day number in there like February 30th or so.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What are data types of adscy, adsmm and adsdd?

    If each of them were CHAR(4), CHAR(2) and CHAR(2), did you included leading zero?
    For example:
    This(leading zero was included) will be OK. adscy = '2011', adsmm = '01' and adsdd = '03'
    This(left adjust) may be error. adscy = '2011', adsmm = '1 ' and adsdd = '3 '
    This(right adjust) may be error. adscy = '2011', adsmm = ' 1' and adsdd = ' 3'
    This(left adjust, but month was 10, 11 or 12) will be OK. adscy = '2011', adsmm = '12' and adsdd = '3 '

  4. #4
    Join Date
    Mar 2011
    Posts
    4

    Date compare

    Thank you for your replies - Unfortunately, this is how they currently store the dates in separate columns :-(

    Also, I have tries using leading zero's etc to no avail... still looking...

  5. #5
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    pligi, You appear to have a value out of range.

    Check that ADSCY is between 1 and 9999.
    Check that ADSMM is between 1 and 12
    Check that ADSDD is between 1 and 31.

    You probably have a Day value that is out of range for the Month it is in.

    I would check for any ADSDD values = 31 where the ADSMM value is in 2, 4, 6, 9, 11. Also for any ADSMM value = 2 and ADSDD values = 29, 30, 31 and find out if any with 29 is a leap year.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about this query to find invalid adsdd or aamdd?
    Code:
    SELECT a.acct#
         , a.adscy , a.adsmm , a.adsdd
         , CASE
           WHEN a.adsdd > SUBSTR( CHAR( DATE(a.adscy || '-' || a.adsmm || '-01')
                                        + 1 MONTH - 1 DAY , ISO )
                                , 9 , 2 )
           THEN 'adsdd is out of range.'
           ELSE ''
           END AS invalid_adsdd
         , a.aamcy , a.aammm , a.aamdd
         , CASE
           WHEN a.aamdd > SUBSTR( CHAR( DATE(a.aamcy || '-' || a.aammm || '-01')
                                        + 1 MONTH - 1 DAY , ISO )
                                , 9 , 2 )
           THEN 'aamdd is out of range.'
           ELSE ''
           END AS invalid_aamdd
     FROM  ph#file.phpacct a
     WHERE a.amdrc = '33-86-09'
       AND (    a.adsmm IN ('02' , '04' , '06' , '09' , '11')
            AND
                a.adsdd > SUBSTR( CHAR( DATE(a.adscy || '-' || a.adsmm || '-01')
                                        + 1 MONTH - 1 DAY , ISO )
                                , 9 , 2 )
            OR  a.aammm IN ('02' , '04' , '06' , '09' , '11')
            AND
                a.aamdd > SUBSTR( CHAR( DATE(a.aamcy || '-' || a.aammm || '-01')
                                        + 1 MONTH - 1 DAY , ISO )
                                , 9 , 2 )
           )
    Or more generally...
    Code:
    SELECT a.acct#
         , a.adscy , a.adsmm , a.adsdd
         , CASE
           WHEN a.adsdd NOT BETWEEN
                            '01'
                            AND SUBSTR( CHAR( DATE(a.adscy || '-' || a.adsmm || '-01')
                                              + 1 MONTH - 1 DAY , ISO )
                                      , 9 , 2 )
           THEN 'adsdd is out of range.'
           ELSE ''
           END AS invalid_adsdd
         , a.aamcy , a.aammm , a.aamdd
         , CASE
           WHEN a.aamdd NOT BETWEEN
                                '01'
                            AND SUBSTR( CHAR( DATE(a.aamcy || '-' || a.aammm || '-01')
                                              + 1 MONTH - 1 DAY , ISO )
                                      , 9 , 2 )
           THEN 'aamdd is out of range.'
           ELSE ''
           END AS invalid_aamdd
     FROM  ph#file.phpacct a
     WHERE a.amdrc = '33-86-09'
       AND (    a.adsdd NOT BETWEEN
                                '01'
                            AND SUBSTR( CHAR( DATE(a.adscy || '-' || a.adsmm || '-01')
                                              + 1 MONTH - 1 DAY , ISO )
                                      , 9 , 2 )
            OR  a.aamdd NOT BETWEEN
                                '01'
                            AND SUBSTR( CHAR( DATE(a.aamcy || '-' || a.aammm || '-01')
                                              + 1 MONTH - 1 DAY , ISO )
                                      , 9 , 2 )
           )
    Last edited by tonkuma; 03-30-11 at 15:15. Reason: Add a.adscy , a.adsmm , a.adsdd , a.aamcy , a.aammm , a.aamdd to the results.

  7. #7
    Join Date
    Mar 2011
    Posts
    4

    Getting same error

    Hmm... Tonkuma, thank you for the code but I am getting the same error as before- out of range. Wonder if there is some corruption going on here.

  8. #8
    Join Date
    Mar 2011
    Posts
    4

    Found it!!

    Problem was caused by bad data - some records contained zeros in the date columns. When I exclude this data, the query works fine. Thanx to all who replied, it was appreciated!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •