Results 1 to 7 of 7

Thread: Converting Julian dates using SQL in DB2

  1. #1
    New Member
    Join Date
    Jun 2004
    Location
    Cambs, UK
    Posts
    21

    Converting Julian dates using SQL in DB2

    Hi all,

    Has anyone managed to convert julian dates to "real" dates in SQL using DB2?

    There's several of us here who've done it any which way on Oracle databases, but can't get anything to work on DB2 short of exporting to Excel and converting there...

    I'd appreciate a silver bullet if anyone's got one!

    Cheers,

    Andy.

    XE / 8.10 / 8.11 / 8.12 9.02 / 9.1

  2. #2
    New Member
    Join Date
    Apr 2002
    Posts
    21

    Re: Converting Julian dates using SQL in DB2

    This works when using the 'run an SQL script' from iNavigator:

    REPLACE(CHAR(DATE(SUBSTR(DIGITS(abupmj+1900000),2, 7)),iso),'-','')


    -- Example: this is selecting the address book number & date updated from the F0101
    select aban8, REPLACE(CHAR(DATE(SUBSTR(DIGITS(abupmj+1900000),2, 7)),iso),'-','') from testdta.F0101
    XE/ERP8/8.9 - Win2k - SQL Server 2k * E810/E811/E8111/E812/E90 - Win2003 - SQL Server 2005
    E91 - Win2008 - Oracle (and an iSeries, too)

  3. #3

    Re: Converting Julian dates using SQL in DB2

    Hi Andy,

    Here you go:

    SELECT
    ABUPMJ,
    CAST(CAST( ((CAST( (ABUPMJ - MOD(ABUPMJ, 1000)) / 1000 AS INT) + 1900) * 1000) +
    MOD(ABUPMJ, 1000) AS CHAR(7)) AS DATE) AS REALDATE
    FROM TESTDTA.F0101

    Should result in something like this:

    ABUPMJ REALDATE
    --------- -----------
    112335 2012-11-30
    112335 2012-11-30
    112335 2012-11-30
    112335 2012-11-30
    112335 2012-11-30
    Regards,

    Hari Sharma
    Aellius
    EnterpriseOne: Integration (.Net, Web Services) | Output Management | Monitoring

  4. #4
    Member Stewart Schatz's Avatar
    Join Date
    May 2001
    Location
    Greenville, WI USA
    Posts
    155

    Re: Converting Julian dates using SQL in DB2

    Here is what I did and a IBM site that may help:

    E1Tips.com - EnterpriseOne (E1) Batch Job Runtimes

    DB2 Basics: Fun with Dates and Times

    Hope they help!
    JDE EnterpriseOne 9.0
    4 Oracle Enterprise Linux Ent Servers
    8 Windows Ent Servers
    Oracle 11g RAC Database
    4 Weblogic HTML Servers
    2 Weblogic RTE Server
    4 Weblogic BSSV Servers
    e1tips.com

  5. #5

    Re: Converting Julian dates using SQL in DB2

    When you say DB2 I am guessing you mean DB2 on the iSeries ?

    You can use the below (I have only used this in the Ops Navigator SQL interface , have not tried it on the green screen (STRSQL))



    select varchar(date(char(1900000+<JULIAN_FIELD_NAME&gt),USA) FROM SCHEMA.TABLE


    For example

    select varchar(date(char(1900000+RLEFFDATE)),USA) as "Effective Date" , varchar(date(char(1900000+RLEXPIRDATE)),USA) as "Expiry Date" FROM
    SY900.F95921 where <your conditions>
    EnterpriseOne Xe to 9.2
    Windows/ Unix / AS400
    Oracle , SQL Server, DB2
    WAS , WLS
    AppWorx, Tidal , SmartScheduler

  6. #6
    New Member
    Join Date
    Jul 2005
    Location
    Brazil - São Paulo
    Posts
    4
    Thank you so much!
    It worked using IBM i Access Data Transfer from Windows version.

    I've changed it just a little bit, to get the Gregorian date in my Region (Brazil) format:
    date(char(1900000+GLDGJ)) as DGJ

  7. #7
    New Member hogn2dise's Avatar
    Join Date
    Jan 2003
    Location
    Minnesota, USA
    Posts
    11
    awesome, simple example from ice_cube210! perfect! thx! only change i made for my use was to go with ISO format.

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 and, or Oracle.