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

Change it to this:

Andy

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):

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