Results 1 to 3 of 3

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
    57
    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

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.