Date formatting from Jullian to regular date?

[Former Member]

Hi,

i am working on a universe which is using IBM DB2 database,

i have a field which is using Jullian Date format (Data type numeric)

it looks like this

2120426

CYYMMDD

i want to convert this to regular date D/m/yyyy

please suggest me how to do this.

Thanks

[Former Member]

thank you all for the reply

i was unable to find the direct way so i used this formula

TO_DATE(SUBSTR(cast(JULLIAN.DATE  AS VARCHAR(11)),4,2)||'/'||SUBSTR(cast(JULLIAN.DATE AS  VARCHAR(11)),6,2)||'/'||(SUBSTR(cast(JULLIAN.DATE AS  VARCHAR(11)),1,1)||'0'||SUBSTR(cast(JULLIAN.DATE AS  VARCHAR(11)),2,2)),'MM/dd/yyyy')

THIS HAVE CONVERTED MY DATE TO MM/DD/YY FORMAT

2120426   to 04/26/2012   

i used list line in the formula to insert 0 after 2 to make it look like 2012

(SUBSTR(cast(JULLIAN.DATE AS VARCHAR(11)),1,1)||'0'||SUBSTR(cast(JULLIAN.DATE AS VARCHAR(11)),2,2))

[former_member189638]

Did you try creating the a Date Object in the Universe with formula DATE(Table.Jullian Date) ??

Also I think the Regular Date column should already be there in the database table.So isn't it better to bring that column into the universe directly rather than creating it at the Universe level?

[Former Member]

Thanks Rakesh for quick reply,

i tried to_Date(Object,'J') --i am getting error in sql execution

   and date(jullian object) --i am getting wrong date, suppose i have jullian date 2120426

04/26/12 (MM/YY/DD) but it is showing up as 5/30/93

i dont understand what is getting wrong.

[Former Member]

The calendar date for  2120426 is indeed  CE 1093 May 31UT  Tuesday (now it might vary marginally based on time zone)

You can use the below link to check and convert Julian to calendar date

http://aa.usno.navy.mil/data/docs/JulianDate.php

[Former Member]

thank you all for the reply

i was unable to find the direct way so i used this formula

TO_DATE(SUBSTR(cast(JULLIAN.DATE AS VARCHAR(11)),4,2)||'/'||SUBSTR(cast(JULLIAN.DATE AS VARCHAR(11)),6,2)||'/'||(SUBSTR(cast(JULLIAN.DATE AS VARCHAR(11)),1,1)||'0'||SUBSTR(cast(JULLIAN.DATE AS VARCHAR(11)),2,2)),'MM/dd/yyyy')

[Former Member]

This function should work as well ...

[Former Member]

Try using to_date(2120426,'J')

I don't have a DB2 to test the code works ... however checked in HANA with the below SQL which does very similar and its is working there..

select to_date(to_char(current_date,'J'),'J') from dummy