DSXchange

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.

You need to use IConv and OConv.

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

regards

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

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.

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.

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.

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.

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.

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.

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.

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

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 ****