Results 1 to 3 of 3

Thread: SQL for Julian date Converion

  1. #1
    New Member
    Join Date
    Nov 2002
    Posts
    32

    SQL for Julian date Converion

    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
    E1 9.1 update 2 Oracle

  2. #2
    Senior Member Larry_Jones's Avatar
    Join Date
    Nov 2000
    Location
    Spokane, WA, USA
    Posts
    3,025
    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';
    Larry Jones
    E1 9.1 - TR 9.1.5 on Win 2012 R2. Oracle DB 11.2.04
    WebLogic 12.1.3, Wintel, BI Publisher

  3. #3
    New Member
    Join Date
    Nov 2002
    Posts
    32
    Many thanks Larry.
    E1 9.1 update 2 Oracle

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
The legal restrictions and terms of use applicable to this site are available here.
Use of this site signifies your agreement to the terms of use.
JDELIST is NOT affiliated with JD Edwards® & Company, Oracle or Peoplesoft. Contents of this site are neither endorsed nor approved by JD Edwards® & Company, Oracle or Peoplesoft.