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
Julian to Gregorian(DB2 format YYYYMMDD) date conversion
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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
You could slightly modify Arnd's solution with Iconv(1900+ DateString[1,3]:".01.01","D4YMD" to suit yours
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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: 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.
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
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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
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