Results 1 to 3 of 3

Thread: SQL for Julian date Converion

  1. #1
    New Member
    Join Date
    Nov 2002

    SQL for Julian date Converion

    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

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

  2. #2
    Senior Member Larry_Jones's Avatar
    Join Date
    Nov 2000
    Spokane, WA, USA
    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
    Wintel, BI Publisher

    Migrating to JDE 9.2 and SQL Server 2016

  3. #3
    New Member
    Join Date
    Nov 2002
    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.