Julian to Gregorian(DB2 format YYYYMMDD) date conversion

cmg · Post by **cmg** » Sat Sep 24, 2005 3:14 pm

Hi Folks,
I have a date conversion from julian to DB2 format YYYYMMDDi.e
CYYnnn(105032)
Where C = 0 If Century is 1900 else 1 is Century 2000
YY - Two Digit Year
nnn - The Current Day of the year in 3 digits eg: Jan 1 is 001, Feb 1 is 032

Can you guys assist the logic to implement.
Thanks in advance

cmg

ray.wurlod · Post by **ray.wurlod** » Sat Sep 24, 2005 4:54 pm

Welcome aboard! :D

Search is your friend. For example this postasks exactly your question though in the server job forum. Search, also, in the Parallel Job Developer's Guide for "Julian" to learn what date conversion functions exist.

Bala R · Post by **Bala R** » Sat Sep 24, 2005 5:29 pm

This post and this from Ray :D should help you.

You could slightly modify Arnd's solution with Iconv(1900+ DateString[1,3]:".01.01","D4YMD" to suit yours

cmg · Post by **cmg** » Sat Sep 24, 2005 5:37 pm

Thanks Ray n Bala.
I have a function in PX like DatefromJulianDay whether it works.
Hope this will not work
Any suggestion?

ray.wurlod · Post by **ray.wurlod** » Sat Sep 24, 2005 11:48 pm

Beware that the function name will depend on where you are executing. For example the equivalent in a Modify stage is called date_from_julian_day (all lower case, with underscores). You have found a function that is for a Transformer stage.

Yes, that will give you the ordinal day number in the year, but you still have some transformation to do to handle your two digit year and one digit century parts. How you do this will depend on what data type you want to use. For example, as a uint32:

Code: Select all

century * 100000 + year * 1000 + julianday

and then you need to assert that you want to keep the leading zero. Or you could treat the whole thing as a string, but will need to convert the numeric date parts into string.

cmg · Post by **cmg** » Mon Sep 26, 2005 9:47 pm

Ray,
Can you give me a detail example.
like how do i convert 105030 to 2005/01/30
I tried to use the function in transformer date from juliandate but iam getting output as **********.
Any help is appreciated

Thanks in advance!
cmg

clshore · Post by **clshore** » Thu Oct 06, 2005 5:51 pm

For dates in years between 1900 and 9999, you can convert in DB2 like this:

DB2 internal date = DATE(RTRIM(CHAR( D1 + 1900000 )))

where D1 is the julian-like-date

Using your example of 105030,

DATE(RTRIM(CHAR( 105030 + 1900000 )))
DATE(RTRIM(CHAR( 2005030 )))
DATE(RTRIM('2005030 ')) -- DB2 creates 11 char right padded string
DATE('2005030')
2005/01/30 as DB2 internal date

BTW, this is much simpler in Oracle:
to_date( D1 + 1900000, 'YYYYDDD')

Carter