Results 1 to 6 of 6
  1. #1
    Join Date
    May 2012
    Posts
    155

    Unanswered: All datas are okay, but still error in date arithmetic

    Hallo everyone,

    if I run this command, it works perfectly:

    Code:
    SELECT date_column + 23 months 
    FROM my_table 
    FETCH
           FIRST 100 rows only
    But if I run the command on the entire rows:

    Code:
    SELECT date_column + 23 months 
    FROM my_table
    It gives me the classical error statement:
    A datetime arithmetic operation or a datetime scalar function has a result that is not within the valid range of dates..

    I am so confusing , because I have checked the date_column entries. It all seems correct. Anyone has a clue, how I can fix this?

    Thank you

    Regards,
    Ratna

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Try:
    Code:
    SELECT Count(*)
       FROM my_table
       WHERE  date_column IS NULL
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    May 2012
    Posts
    155
    Hallo Pat Phelan,

    thank you for the quick reply. I think, I found the problem. I have some dates, which in fact get my results out of range:

    9999-12-31
    ...
    ...

    How can I tell db2 not to look up these unvalid dates for the SQL-Command?
    I am thinking about using CASE-Statement, but I dont know how to tell db2, how to exclude these dates.

    Thank you for the helps.

    Regards,
    Ratna

  4. #4
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    i think you SELECT clause has been abbreviated for this topic.

    if you want to exclude them, use a WHERE clause.

    If you use CASE in the SELECT clause, you could either
    CAST the incremented date to character data
    or
    return 'NO I-DATE'
    Dick Brenholtz, Ami in Deutschland

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you simply want to exclude the offending rows entirely, you could use:
    Code:
    SELECT date_column + 23 months 
       FROM my_table 
       WHERE  date_column < Date('9999-12-31') - 23 MONTHS
       FETCH  FIRST 100 rows only
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    May 2012
    Posts
    155
    Thank you very much Pat Phelan, it works.

Posting Permissions

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