Results 1 to 6 of 6
  1. #1
    Join Date
    May 2011
    Posts
    5

    Unanswered: The syntax of the string representation of a datetime value is incorrect

    i have a query:
    select ID from tb where date_from
    NOT IN ('0001-01-01','')
    AND date_to IN ('0001-01-01','');

    The query should return all the ID with date_from that is not 0001-01-01 or ' ' and date_to that is '0001-01-01' or ' '.
    I got an error saying,
    The syntax of the string representation of a datetime value is incorrect.. SQLCODE=-180, SQLSTATE=22007
    I am using db2 for windows,version v9.7. please help me fix the query thanks

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You may be trying to use '' as null. This works in Oracle, but doesn't work in DB2 unless maybe you have Oracle compatibility tuned on, or maybe they changed Db2 to allow it in 9.7.

    Try

    where date_from
    NOT IN ('0001-01-01',null)
    AND date_to IN ('0001-01-01',null);
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    May 2011
    Posts
    4

    Lightbulb re:The syntax of the string representation of a datetime value is incorrect

    you could try this instead:

    AND (date_from <> '0001-01-01' or NOT(date_to IS NULL))
    AND (date_to = '0001-01-01' or date_to IS NULL)

    cheers

  4. #4
    Join Date
    May 2011
    Posts
    5

    The syntax of the string representation of a datetime value is incorrect Reply to Thr

    thank you marcus, i have tried the null but it only returns nothing

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    AND (date_from <> '0001-01-01' or NOT(date_to IS NULL))
    AND (date_to = '0001-01-01' or date_to IS NULL)
    "or" should be "AND", like this...
    AND (date_from <> '0001-01-01' AND NOT(date_from IS NULL))
    AND (date_to = '0001-01-01' or date_to IS NULL)

    If "date_from <> '0001-01-01'" is true, it implies date_from IS NOT NULL.
    (If date_from is null, "date_from <> '0001-01-01'" is UNKNOWN.)
    So, this will be enough.
    AND date_from <> '0001-01-01'
    AND (date_to = '0001-01-01' or date_to IS NULL)

  6. #6
    Join Date
    May 2011
    Posts
    5
    i got it. thanks artemis and tonkuma..

Tags for this Thread

Posting Permissions

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