julian date conversion
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 37
- Joined: Mon Jan 24, 2005 10:12 am
julian date conversion
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.
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.
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
use sub string function
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.
of course you can use iconv and oconv, but it will cost performance more.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
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.
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.
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
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.
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.
![Wink :wink:](./images/smilies/icon_wink.gif)
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
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 ****
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 ****