Page 1 of 1
Julian to Gregorian(DB2 format YYYYMMDD) date conversion
Posted: Sat Sep 24, 2005 3:14 pm
by cmg
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
Posted: Sat Sep 24, 2005 4:54 pm
by ray.wurlod
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.
Posted: Sat Sep 24, 2005 5:29 pm
by Bala R
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
Posted: Sat Sep 24, 2005 5:37 pm
by cmg
Thanks Ray n Bala.
I have a function in PX like DatefromJulianDay whether it works.
Hope this will not work
Any suggestion?
Posted: Sat Sep 24, 2005 11:48 pm
by ray.wurlod
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.
Posted: Mon Sep 26, 2005 9:47 pm
by cmg
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
Posted: Thu Oct 06, 2005 5:51 pm
by clshore
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