Qlik Community

QlikView Documents

QlikView documentation and resources.

Announcements
Document boards are being consolidated, this board no longer allows NEW documents READ MORE

Oracle Julian Date Conversion

cancel
Showing results for 
Search instead for 
Did you mean: 
joseduque
Partner
Partner

Oracle Julian Date Conversion

Hi All,

For New and Old users the julian date is a 6 digit date that all oracle databases have. So to convert this julian I attached the transformation:

To Date:

date(yearstart(makedate((((WASTRT - fmod(WASTRT,1000)) / 1000) + 1900))) + (fmod(WASTRT,1000) - 1)) as Date,

To Year: 
year(date(yearstart(makedate((((WASTRT - fmod(WASTRT,1000)) / 1000) + 1900))) + (fmod(WASTRT,1000) - 1))) as Year

To Month:
Month(date(yearstart(makedate((((WASTRT - fmod(WASTRT,1000)) / 1000) + 1900))) + (fmod(WASTRT,1000) - 1))) as Month

To Day:
Day(date(yearstart(makedate((((WASTRT - fmod(WASTRT,1000)) / 1000) + 1900))) + (fmod(WASTRT,1000) - 1))) as Day

Hope This Helps

Tags (2)
Comments
zhadrakas
Specialist II
Specialist II

Nice one. I would just add the SQL commands if someone want to do the Transformation in Backend:

if your date is a String:

select TO_DATE(TO_CHAR(to_number('115032')+1900000),'YYYYDDD') jdedate from dual;

if your date is a number:

select TO_DATE(TO_CHAR(115032+1900000),'YYYYDDD') jdedate from dual;

Version history
Last update:
‎2016-12-21 04:12 PM
Updated by: