julian date conversion

[supernova2005]

Hi everyone,

I need to convert a julian date with formate yyyyddd to regular date formate, for example,

if the input is 2005001, then the output is 01/01/2005. Does datastage have a built-in function to do that, otherwise I have to write my own logic, which is very complicated, because I have to consider leap years.

Your suggestions will be appreciated.

thanks.

[Sainath.Srinivasan]

You need to use IConv and OConv.

[amsh76]

Use ICONV to convert the Julian Date into DataStage internal format..and then use OCONV to convert into the format you want.

regards

[Sainath.Srinivasan]

Can we move this topic to Server section rather than FAQ.

[T42]

For EE - there are already a few functions that converts to JulianDay and back to Date. All you need to do is convert the string into Date.

[changming]

if the data is clean, i mean they are all valid date, the eastest way is to use sub string function. string[].
of course you can use iconv and oconv, but it will cost performance more.

[ray.wurlod]

Show us how you convert 001 to 01/01 and 059 to 02/28 and 060 to ... umm ... is it 02/29 or 03/01, using substring.

[Sainath.Srinivasan]

The code you require is as below

memYear = Arg1[1, 4]
memJJJ = Arg1[5, 3]

memDSDate = IConv(memYear : '-01-01', 'D-YMD[4,2,2]') - 1 + memJJJ
* -1 to check until last year Dec 31st

Ans = OConv(memDSDate, 'D/DMY[2,2,4]')


Stick it into a routine and reference it from your jobs.

There are other pre-built codes - such as YEAR.TAG - that does part of this functionality. Anyhow, this will be one call for your job.

[Sainath.Srinivasan]

Just remember after pressing the submit button. You can do it in simple way of using IConv(YYYYJJJ, 'DYJ') rather than the full routine and then follow it with your OConv.

[chulett]

A couple of quick points...

On the substring issue, Ray meant using only the substring operator - which is what was implied by changming. All fine and dandy when rearranging 'normal' dates, but not appropriate for converting from Julian.

Also, you *can* edit your own posts, so rather than post a correction simply go back and fix it. You can do this even if it's been replied to, unlike deleting a post which can only be done if no-one has replied. FYI.

[Sainath.Srinivasan]

Thanks for the tip about changing own post.

PS - I did not try to prove substring theory. My intention, as you can see from the second post in this thread, was to provide a response to supernova2005's requirement.

[chulett]

Ah... sorry. Since you replied immediately after that using [] to hack up the input argument, I figured you were.

[Sainath.Srinivasan]

No worries!!

Anyhow, as you had challenged, please see the simple (!?) code to perform the same WITHOUT IConv or OConv.


**** Beginning of Routine ****

* Ans = OConv(IConv(Arg1, 'DYJ'), 'D/DMY[2,2,4]')

memYYYY = Arg1[1, 4]

memDaysRemaining = Arg1[5, 3]

If memDaysRemaining = 0 Then
memYYYY = memYYYY - 1
memMM = 12
memDD = 31
End

If Mod(memYYYY, 4) = 0 And Mod(memYYYY, 400) <> 0 Then
memLeapDay = 1
End
Else
memLeapDay = 0
End

memMonthDays<1> = 31
memMonthDays<2>= 28 + memLeapDay
memMonthDays<3>= 31
memMonthDays<4>= 30
memMonthDays<5>= 31
memMonthDays<6>= 30
memMonthDays<7>= 31
memMonthDays<8>= 31
memMonthDays<9>= 30
memMonthDays<10>= 30
memMonthDays<11>= 30
memMonthDays<12>= 30



For memThatMonth = 1 To 12 Step 1
If memDaysRemaining > 0 And memDaysRemaining <= memMonthDays<memThatMonth> Then
memMM = memThatMonth
memDD = memDaysRemaining - 0
memDaysRemaining = 0
Exit
End
Else
memDaysRemaining = memDaysRemaining - memMonthDays<memThatMonth>
End
Next memThatMonth

If memDaysRemaining > 0 Then
memMM = 99
memDD = 99
End

Ans = Fmt(memDD, "2'0'R") : '/' : Fmt(memMM, "2'0'R") : '/' : Fmt(memYYYY, "4'0'R")
***** End of Routine ****