• Welcome to the upgraded JDELIST forum and thank you for your patience.
    Please restrict discussions and issues regarding the new forum software to the Off Topic forum. We will be monitoring that forum for issues.
    If you have trouble logging in, please reset your password using the forgotten password form: https://www.jdelist.com/community/index.php?lost-password/
    If you are unable to successfully reset your password, please contact us: Click here!
    We hope that you enjoy the upgraded forum.
  • Introducing Dark Mode! Switch by clicking on the lightbulb icon next to Search or by clicking on Default style at the bottom left of the page!

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
 

lstanton

Member
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
 
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())
 

slandess

Member
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
 
Top