Results 1 to 10 of 10

Thread: CHAR to DATE

  1. #1
    Join Date
    Jan 2010
    Posts
    6

    Unanswered: CHAR to DATE

    Hi All

    I am trying to write a query to only return rows between dynamic dates. However the date im using is actually a char. If I put DATE() around the char it returns '++++++++++++++'

    Could this be to do with the characters set being different on the server than the database?

    Any suggestions appreciated
    Thanks in advance

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Casting a CHAR-string to a DATE will never return '++++++++++++++' in DB2. So it would be helpful if you could tell us which system you are using in which version and what the actual SQL statements are.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Jan 2010
    Posts
    6
    I am using V5R1M0. A test I done on the char to see the result was:
    SELECT DATE(LOGDAT) FROM QPRLOG this returned '++++++++++++++'

    The full query im trying to write is:
    SELECT LOGLND, LOGTRN, LOGRQS, LOGDAT FROM QPRLOG WHERE LOGRQS = '001' AND DATE (days (LOGDAT)) - days (current date - 7 days) and (days (LOGDAT)) - days (current date - 1 days)

    Thanks

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    AND DATE (days (LOGDAT)) - days (current date - 7 days) and (days (LOGDAT)) - days (current date - 1 days)
    There were syntax errors.
    What is your requirement with this predicates?
    Is that something like this?
    "LOGDAT is between current date - 7 days and current date - 1 day"
    (This is not right SQL syntax. I'm asking your intention.)

  5. #5
    Join Date
    Jan 2010
    Posts
    6
    Hi Tonkuma,

    Im trying to see all rows for the last 7 days.

    Thanks

  6. #6
    Join Date
    Jan 2010
    Posts
    6
    I've just noticed I pasted the wrong query it should have been:

    SELECT DATE(LOGDAT) FROM QPRLOG WHERE LOGRQS = '001' AND DATE(LOGDAT) BETWEEN days (current date - 7 days) AND days (current date - 1 days)

    The problem is that DATE(LOGDAT) returns '++++++++++++' and is not converted to a date but im not sure why this happens

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What are actual data of LOGDAT?
    Could you provide the result of ...
    SELECT LOGDAT FROM QPRLOG FETCH FIRST 10 ROWS ONLY

    Im trying to see all rows for the last 7 days.
    Please try...
    SELECT LOGLND, LOGTRN, LOGRQS, LOGDAT FROM QPRLOG WHERE LOGRQS = '001' AND DATE(LOGDAT) BETWEEN (current date - 7 days) AND (current date - 1 day)
    "DATE(LOGDAT)" may be required to change.
    Last edited by tonkuma; 01-27-10 at 06:34. Reason: Change selected column list in the last sample query.

  8. #8
    Join Date
    Jan 2010
    Posts
    6
    thanks for all your help I finally got it to work:

    Select DATE(SUBSTR(LOGDAT,1,4)||'-'||
    SUBSTR(LOGDAT,5,2)||'-'||
    SUBSTR(LOGDAT,7,2)
    )

    From QPRLOG
    WHERE LOGRQS = '001'
    AND DATE(SUBSTR(LOGDAT,1,4)||'-'||
    SUBSTR(LOGDAT,5,2)||'-'||
    SUBSTR(LOGDAT,7,2)
    ) BETWEEN current date - 7 days AND current date

    Really don't know why DATE() didn't work but this solution is fine.
    Again thanks for your time.
    Sinead

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Really don't know why DATE() didn't work ...
    Looking into your code, I guessed that the format of LOGDAT is 'yyyymmdd'.
    The format is not supported as string representation of date data.

    Please see the manual "DB2 Universal Database for iSeries SQL Reference Version 5".
    Chapter 2. Language Elements ---> Data Types ---> Datetime Values ---> String Representations of Datetime Values

  10. #10
    Join Date
    Jan 2010
    Posts
    6
    yes that is the format, so thats it so. Thanks

Posting Permissions

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