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

Fsldiscover

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