Qlik Community

QlikView Documents

QlikView documentation and resources.

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

Oracle Julian Date Conversion

Showing results for 
Search instead for 
Did you mean: 

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)
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: