• Introducing Dark Mode! Switch by clicking on the lightbulb icon next to Search or by clicking on Default style at the bottom left of the page!

SQL for Julian date Converion

sgpg

Active Member
Hello,
not sure if this is the right forum for a JDE SQL question but I can be told if wrong!

I have to convert a julian date and I am using the example below

Decode(PRODDTA.F03B11.RPDDJ,
0,
Null,
To_Date(To_Char(PRODDTA.F03B11.RPDDJ + 1900000), 'yyyyddd')) As "Due Date", this converts a Julian (117001 to 01-Jan-17) but I need this to be 20170101 (1st January 2017 UK.
thanks
 

Larry_Jones

Legendary Poster
A date is a date. Its value, once converted to the DATE datatype, is standard.
However the formatted display format depends on several factors - such as what tool you're using to view the results with, your client OS localization settings, etc.

If, as it appears, you're using SQLPLUS or SQL Worksheet to perform your query you're at the mercy of the Database's default display format for dates (DD-MON-YY). This is configurable though at the Session level. Use:

alter session set nls_date_format = 'YYYYMMDD';
 
Top