Results 1 to 4 of 4

Thread: condition to return row where Julian day is > current date in DB2 in i7.2

  1. #1

    condition to return row where Julian day is > current date in DB2 in i7.2

    Hi : excuse me if I ask dump question. I am new in DB2 and Julian day format

    I google around and just cannot fine a answer/example.
    I am writing a simple SQL. The goal is to return any row that is NOT expired yet.

    select * FROM F1501B where nevred >= currdate();

    As you already know the nevred is in Julian. Can Julian be compared ? What is the "right" way to write this condition

    thanks in advanced

  2. #2
    Hello,

    In case you have not solved this yet, or someone else comes looking.

    To get the equivalent of today's Gregorian date in the JD Edwards Julian date format you would use

    (DATEPART(yy, getdate()) - 1900) * 1000 + DATEPART(dy, getdate())

    so for your select statement you would use

    select * FROM F1501B where nevred >= (DATEPART(yy, getdate()) - 1900) * 1000 + DATEPART(dy, getdate())

    Thanks
    Lyle Stanton

    E1 9.0 | TR 9.1.5
    E1 9.2 | TR 9.2.3.4

  3. #3
    Member
    Join Date
    Mar 2009
    Location
    Phoenix, AZ USA
    Posts
    58
    Hi,
    While Lyle's SQL works great in SQL Server, if you're looking for the DB2 equivalent, use
    select * FROM PRODDTA.F1501B where nevred >= (YEAR(curdate()) - 1900) * 1000 + DAYOFYEAR(curdate())
    Regards,
    Kim Schmidt

    - E1 9.1.4 ,TR 9.2.2 , iSeries, DB2
    - formerly Xe, 8.0, SQL Server, Oracle

  4. #4
    Member slandess's Avatar
    Join Date
    May 2001
    Location
    Austin, Texas
    Posts
    196
    Kim -
    Here is another way to do it using DB2 SQL:

    (DATE(STRIP(DIGITS(JDEJULIAN +1900000),L,'0'))
    converts field JDEJULIAN to a DB2 date field)

    Select *from F1501B
    Where DATE(STRIP(DIGITS(NEVRED+1900000),L,'0')) > CURRENT_DATE
    Steve Landess
    Austin, Texas
    512-289-0387

    AS/400/iSeries/i5/System i/IBM Power
    V5R4/IBM i 6.1/IBM i 7.1
    SQLServer / Oracle / DB2 for i
    JDE World Software / RPG400 / RPGIV
    Java / SQL / EDI / XML
    Eclipse / WDSC / RDi / RDp / SAP Netweaver

    steve.landess@gmail.com

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.