Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER

Oracle Julian Date Conversion

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
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
Revision #:
1 of 1
Last update:
‎2016-12-21 04:12 PM
Updated by: