Regular Date To JulianDate

[lak4u]

I need to convert DataStage current Date (2009-05-13) to JulianDate

Example:

from source I am getting Date: 109096

i.e. 1900+109 =2009
096 = 0406 (April 06)

I am trying below but I couldn't get it...
Oconv(Iconv("2009-04-06","D4-YMD[4,2,2]"),"DJ")

FOR JulianDateToTimeStamp I am able to convert ...
OCONV(ICONV(%JulianDate% + 1900000, "D4-"), "D4-YMD[4,2,2]")


Please anybody can help?

Thanks in advance

[ArndW]

Can you break up the problem into 2 steps, first the ICONV and then the OCONV?
When you get "109096" what function do you use and do you convert it to a display date or a DataStage internal date?

[lak4u]

Can you break up the problem into 2 steps, first the ICONV and then the OCONV?
When you get "109096" what function do you use and do you convert it to a display date or a DataStage internal date? ...

Thanks for reply.

I am able to converting
OCONV(ICONV("109096" + 1900000, "D4-"), "D4-YMD[4,2,2]")

I am passing my Column name which is "109096" and it displays Date = 2009-04-06

Now I need just reverse if I pass 2009-04-06 then I need "109096"

[ArndW]

Have you tried the inverse function,

Code: Select all

OCONV(ICONV("2009-04-06","D4-YMD[4,2,2]")[b]-1900000[/b],"DJ")[code]

[lak4u]

Have you tried the inverse function,

Code: Select all

OCONV(ICONV("2009-04-06","D4-YMD[4,2,2]")[b]-1900000[/b],"DJ")[code] ...[/quote]

Can you please elaborate ? what is [code] ?

[chulett]

Broken formatting tag, ignore it. Should have looked like this:

Code: Select all

OCONV(ICONV("2009-04-06","D4-YMD[4,2,2]") -1900000 ,"DJ")

[lak4u]

Have you tried the inverse function,

Code: Select all

OCONV(ICONV("2009-04-06","D4-YMD[4,2,2]")[b]-1900000[/b],"DJ")[code] ...[/quote]

I tried this and got value "114", I never used inverse function,can you please tellme the how to use this [code]?

Thaks

[lak4u]

Broken formatting tag, ignore it. Should have looked like this:

Code: Select all

OCONV(ICONV("2009-04-06","D4-YMD[4,2,2]") -1900000 ,"DJ")

...

I tried this.I got value "114"

[ArndW]

I don't have access to DS or the manuals, but I kind of doubt your 1900000 addition/subtraction - that number looks wrong. Where did you get that and are you certain it works?

[lak4u]

I don't have access to DS or the manuals, but I kind of doubt your 1900000 addition/subtraction - that number looks wrong. Where did you get that and are you certain it works? ...

Thanks.
I am not sure about this 1900000 but is showing currect result

FOR JulianDateToDate I am able see currect result...
OCONV(ICONV("109096" + 1900000, "D4-"), "D4-YMD[4,2,2]")

result : 2009-04-06

[Kryt0n]

I need to convert DataStage current Date (2009-05-13) to JulianDate

Example:

from source I am getting Date: 109096

i.e. 1900+109 =2009
096 = 0406 (April 06)

Are you trying to convert a value from source or current date?

Assuming your source is always six characters, form your date (doing as you are doing), convert to internal format and add 2439856 (this being 31 December 1967 in Julian Date).

The OCONV format "DJ" gives Julian Year Date and not Julian Date that is why you get 114.

[chulett]

The OCONV format "DJ" gives Julian Year Date and not Julian Date that is why you get 114.

Also known as an "Ordinal" date - the day number of that year.