Date
Moderators: chulett, rschirm, roy
Date
Hi,
I've date coming from DB2-AS400 as Julian . I'm able to succefully convert dates with using Functions
Ans=Oconv(Iconv(TheDate, "DYJ"), "D-YMD[4,2,2]")
Input - 90313
output - 1990-11-09
But I've dates coming in format 104314 i.e. 2004-11-09 for which above routine do not works. To make it simple where the i/p julian format is of 5 lenght the routine works but when it the sourcr come as 6 length it fails ot I'm not able to convert data for year above 1999
Can someone suggest how to convert the dates in this format.
Regards,
Munish
I've date coming from DB2-AS400 as Julian . I'm able to succefully convert dates with using Functions
Ans=Oconv(Iconv(TheDate, "DYJ"), "D-YMD[4,2,2]")
Input - 90313
output - 1990-11-09
But I've dates coming in format 104314 i.e. 2004-11-09 for which above routine do not works. To make it simple where the i/p julian format is of 5 lenght the routine works but when it the sourcr come as 6 length it fails ot I'm not able to convert data for year above 1999
Can someone suggest how to convert the dates in this format.
Regards,
Munish
Y2K alert...
Code: Select all
If LEN(TheDate) = 5 Then
Ans=Oconv(Iconv(TheDate, "DYJ"), "D-YMD[4,2,2]")
Else If LEN(TheDate) = 6 Then
Ans=Oconv(Iconv(TheDate[2,5], "DYJ"), "D-YMD[4,2,2]")
Else
Ans=""
End
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Hi Ken ,
This is same as
Ans=Oconv(ICONV(Right(TheDate,5), "D-YMD"), "D-YMD[4,2,2]")
but this will not work i.e what will happen if the date is 1901-12-11
I may be wrong but if we can put the julian format for 1901-12-11
it will fail , i think any year below 1930 fails
as 30313 = 1930-11-09
but as you change the value to
20313 = 2020-11-08
29313 = 2029-11- 09
so how come we get the value when year is below 1930 i.e what will be the julian format for year 1929 - 11 -12
Regards,
Muneesh
[quote="kcbland"]Y2K alert...
[code]If LEN(TheDate) = 5 Then
Ans=Oconv(Iconv(TheDate, "DYJ"), "D-YMD[4,2,2]")
Else If LEN(TheDate) = 6 Then
Ans=Oconv(Iconv(TheDate[2,5], "DYJ"), "D-YMD[4,2,2]")
Else
Ans=""
End[/code][/quote]
This is same as
Ans=Oconv(ICONV(Right(TheDate,5), "D-YMD"), "D-YMD[4,2,2]")
but this will not work i.e what will happen if the date is 1901-12-11
I may be wrong but if we can put the julian format for 1901-12-11
it will fail , i think any year below 1930 fails
as 30313 = 1930-11-09
but as you change the value to
20313 = 2020-11-08
29313 = 2029-11- 09
so how come we get the value when year is below 1930 i.e what will be the julian format for year 1929 - 11 -12
Regards,
Muneesh
[quote="kcbland"]Y2K alert...
[code]If LEN(TheDate) = 5 Then
Ans=Oconv(Iconv(TheDate, "DYJ"), "D-YMD[4,2,2]")
Else If LEN(TheDate) = 6 Then
Ans=Oconv(Iconv(TheDate[2,5], "DYJ"), "D-YMD[4,2,2]")
Else
Ans=""
End[/code][/quote]
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
The internal format is a negative number for dates prior to the base date.
So, if the internal format of 2004-11-09 is 104314 then your base date is 104314 days prior to that.returns 1719-04-04, which is the base date for this particular Julian scheme. (I checked it with "D-YMD[6,2,2]" to be sure.)
DataStage's base date is 1967-12-31.
To convert one of your Julian dates into a DataStage internal format, you need to adjust by the difference between the two base dates (-90851 days) then use OCONV. For example returns 2004-11-09
Magic, innit?
So, if the internal format of 2004-11-09 is 104314 then your base date is 104314 days prior to that.
Code: Select all
OCONV(ICONV('2004-11-09','DYMD') - 104314, 'D-YMD[4,2,2]')
DataStage's base date is 1967-12-31.
To convert one of your Julian dates into a DataStage internal format, you need to adjust by the difference between the two base dates (-90851 days) then use OCONV. For example
Code: Select all
OCONV(104314 - 90851, "D-YMD[4,2,2]")
Magic, innit?
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.
Thanks Ray ,
It solves the problem for the PArticular date but how to use your logic at runtime as the logic given the 90851 is hardcoded for only one type of date 104314, it will be impossible for calculating the difference for each type of date in data i.e. can we pass it as an argument it will be great help.
Regards,
Munish
It solves the problem for the PArticular date but how to use your logic at runtime as the logic given the 90851 is hardcoded for only one type of date 104314, it will be impossible for calculating the difference for each type of date in data i.e. can we pass it as an argument it will be great help.
Regards,
Munish
ray.wurlod wrote:The internal format is a negative number for dates prior to the base date.
So, if the internal format of 2004-11-09 is 104314 then your base date is 104314 days prior to that.returns 1719-04-04, which is the base date for this particular Julian scheme. (I checked it with "D-YMD[6,2,2]" to be sure.)Code: Select all
OCONV(ICONV('2004-11-09','DYMD') - 104314, 'D-YMD[4,2,2]')
DataStage's base date is 1967-12-31.
To convert one of your Julian dates into a DataStage internal format, you need to adjust by the difference between the two base dates (-90851 days) then use OCONV. For examplereturns 2004-11-09Code: Select all
OCONV(104314 - 90851, "D-YMD[4,2,2]")
Magic, innit?
Is "30" 1630, 1730, 1830, 1930 or 2030? When a date does not have century information, you have only can guess at a 100-year window.
You must have a year picked to be the transition point, in this case check your uvconfig file as you are defaulted to 1930. So, tell me, how would you know if 30-jan-01 is really 1930-jan-01 or 2030-jan-01? You can set your uvconfig to treat 01 as 1901, but you'll never get a 2001 date without fully qualifying the century.
You must have a year picked to be the transition point, in this case check your uvconfig file as you are defaulted to 1930. So, tell me, how would you know if 30-jan-01 is really 1930-jan-01 or 2030-jan-01? You can set your uvconfig to treat 01 as 1901, but you'll never get a 2001 date without fully qualifying the century.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Ray is suggesting a mathematical way of taking care of a 5 digit number rolling to 6 digits because a junior-skillset programmer allowed a Y2K issue to come your way.
If you have a 6 digit number, make the assumption that you're in the 2000 century and adjust the number to a include the appropriate century? You might as well hardcode the century fix. If 104 is really 2004, then make it so! If 04 is really 1904, then make it so! Turn your date into 2004-xyz if it makes it easier to understand.
If you have a 6 digit number, make the assumption that you're in the 2000 century and adjust the number to a include the appropriate century? You might as well hardcode the century fix. If 104 is really 2004, then make it so! If 04 is really 1904, then make it so! Turn your date into 2004-xyz if it makes it easier to understand.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
My apologies, I misunderstood what the 104319 represented. Now I understand that the rightmost three digits are the ordinal day number in the year, and anything to the left of that is the number of years since 1900.
So you can calculate the year asTo get this into DataStage internal format, use the Julian conversion code
To get that into ISO 8601 format, use Oconv, so that the whole expression is
This approach does not handle dates prior to 1900, but neither does your Julian notation.
So you can calculate the year as
Code: Select all
1900 + Int(JulianDate / 1000)
Code: Select all
Iconv(1900 + Int(JulianDate / 1000) : "/" : Mod(JulianDate, 1000), "DYJ")
Code: Select all
Oconv(Iconv(1900 + Int(JulianDate / 1000) : "/" : Mod(JulianDate, 1000), "DYJ"), "D-YMD[4,2,2]")
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.