Date

shrey3a · Post by **shrey3a** » Tue Nov 23, 2004 2:59 pm

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

kcbland · Post by **kcbland** » Tue Nov 23, 2004 9:23 pm

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

ray.wurlod · Post by **ray.wurlod** » Tue Nov 23, 2004 10:13 pm

Y10K alert...

Code: Select all

OCONV(TheDate, "D-YMD[4,2,2]")

shrey3a · Post by **shrey3a** » Tue Nov 23, 2004 10:43 pm

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]

ray.wurlod · Post by **ray.wurlod** » Wed Nov 24, 2004 1:01 am

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.

Code: Select all

OCONV(ICONV('2004-11-09','DYMD') - 104314, 'D-YMD[4,2,2]')

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

Code: Select all

OCONV(104314 - 90851, "D-YMD[4,2,2]")

returns 2004-11-09

Magic, innit?

shrey3a · Post by **shrey3a** » Wed Nov 24, 2004 8:40 am

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

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.
Code: Select all
OCONV(ICONV('2004-11-09','DYMD') - 104314, 'D-YMD[4,2,2]') 
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
Code: Select all
OCONV(104314 - 90851, "D-YMD[4,2,2]")
returns 2004-11-09

Magic, innit?

kcbland · Post by **kcbland** » Wed Nov 24, 2004 9:22 am

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.

kcbland · Post by **kcbland** » Wed Nov 24, 2004 9:28 am

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.

ray.wurlod · Post by **ray.wurlod** » Wed Nov 24, 2004 2:59 pm

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 as

Code: Select all

1900 + Int(JulianDate / 1000)

To get this into DataStage internal format, use the Julian conversion code

Code: Select all

Iconv(1900 + Int(JulianDate / 1000) : "/" : Mod(JulianDate, 1000), "DYJ")

To get that into ISO 8601 format, use Oconv, so that the whole expression is

Code: Select all

Oconv(Iconv(1900 + Int(JulianDate / 1000) : "/" : Mod(JulianDate, 1000), "DYJ"), "D-YMD[4,2,2]")

This approach does not handle dates prior to 1900, but neither does your Julian notation.