Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2009
    Posts
    23

    Unanswered: DB2 SQL - Julian Date equals 0

    I'm using DB2 v9.1 LUW. I have data that has a value of 0 in a Julian Date field. I'm not sure how to work with this. To convert the Julian dates to a current date, I'm doing this:

    DATE(DIGITS(DECIMAL(field + 1900000,7,0)))as CONVER_JULIAN_TO_DATE.

    Now if the Julian date equals 0, I don't know how to convert this. It errors on the above statement. So I was trying to do a case statement unsuccessfully.

    Obviously this does nothing below. But something similiar to this saying when 0 then just 0, else use Date statement above. That is the idea anyway.

    CASE field
    WHEN 0 THEN 0
    ELSE field END as FIELD_NAME,

    I'm getting this SQL0581N The data types of the result-expressions of a CASE expression are not compatible. When attempting to do this.
    Has anyone encountered this problem and can help me?

    Thank you.


    CC

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Change it to this:

    Code:
    CASE field
    WHEN 0 THEN cast(null as date) 
    ELSE field END as FIELD_NAME
    Andy

  3. #3
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    rockycj_dba, This isn't exactly what you asked but I noticed that it appears that your Julian value is the number of days since 1899-12-31 (Since you are adding the value to 1900 00 00). If that is what you want to do, it would be easier to use:

    DATE('1899-12-31') + field DAYS

    If you do this, you can add 0 days without a problem but you will need to keep in mind that a value of '1899-12-31' indicates 0 days. Here is a sample SQL that generates 12 random values (including 0):
    Code:
    WITH TESTTAB (JUL_DT)
      AS (SELECT  0 FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT JUL_DT + 9000 FROM TESTTAB
          WHERE JUL_DT < 99999
         )
    SELECT JUL_DT
         , DATE('1899-12-31') + JUL_DT DAYS AS CONVER_JULIAN_TO_DATE
    FROM TESTTAB

  4. #4
    Join Date
    Apr 2009
    Posts
    23
    Thank you both so much for your help. Andy's response worked for what I needed. And Stealth, thank you for the information on Julian dates. Awesome stuff!

    Thanks again.

    CC

Posting Permissions

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