Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

VIEW usage of current date

Status
Not open for further replies.

ShortyA

MIS
Feb 20, 2002
363
CH
Hi,
I am trying to create a view via ISeries Navigator that will return records that have a date value of today-3months.

Normally I would use the SQL GetDate() function but this doesn't seem to be available via OS/400. Does anyone have any advice on how I can create a view which uses a rolling date ?
Code:
EG
Create view test as
(select crdattim from file1
where crdattim >= currentdate-90)

Thanks for any help.
ShortyA
 
You can try this :

Select MyDate,MyField From Mylib.MyFile
Where MyDate >= (Select Current Date-90 days From Sysibm.Sysdummy1)
 
curdate() ,current date both return to current system date
so you could use something like
select *
from mylib.myfile
where mydate >= (current date - 30 days)

However your example implies that you have a timestamp column. You might have to convert it to a date :

select *
from mylib.myfile
where date(mydate >= (current date - 30 days)

 
Thanks folks.
I used the following as per leveyp's suggestion.
Code:
select * from myfile                         
where date(mydatetime) >=(current date -2 days)



ShortyA
 
Must be loosing it! If your column is a timestamp:
select *
from mylib.myfile
where mydate >= (current timestamp - 30 days)

 
It worked fine on a datetime stamp with the code above. Strange but got the result!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top