VIEW usage of current date

[ShortyA]

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 ?

EG
Create view test as
(select crdattim from file1
where crdattim >= currentdate-90)

Thanks for any help.
ShortyA

 

[HarrisGR]

You can try this :

Select MyDate,MyField From Mylib.MyFile
Where MyDate >= (Select Current Date-90 days From Sysibm.Sysdummy1)

 

[leveyp]

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)

 

[ShortyA]

Thanks folks.
I used the following as per leveyp's suggestion.

select * from myfile                         
where date(mydatetime) >=(current date -2 days)

ShortyA

 

[leveyp]

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

 

[ShortyA]

It worked fine on a datetime stamp with the code above. Strange but got the result!