RE: SQL Date Math

Elvis, does it have to go on EACH element of the date (as below)?

select * from bpcs405cdf/frt
where Dec( digits(Dec(year(curdate() - 365 days),4,0)) ||
digits(Dec(month(curdate() - 365 days),2,0)) ||

digits(Dec(day(curdate() - 365 days),2,0)) ) < refdt

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Elvis Budimlic
Sent: Friday, November 16, 2007 11:37 AM
To: 'Midrange Systems Technical Discussion'
Subject: RE: SQL Date Math

Don,

DB2 supports date arithmetic so something like CURDATE() - 30 DAYS ought
to work in your case.

Fact that you're using CURDATE built-in versus CURRENT_DATE special
register saves you from cross-midnight considerations as DB2 reads
time-of-day clock once per statement execution, which is exactly what
you want.

Elvis

Celebrating 10-Years of SQL Performance Excellence on IBM i5/OS and
OS/400 http://centerfieldtechnology.com/training.asp


-----Original Message-----
Subject: RE: SQL Date Math

Jim,

I'm liking method 2, and got it to work. Thanks! Now, how would I go
about adjusting the curdate - say to subtract 30 days from it before
using it in the comparison?

Don

-----Original Message-----
Subject: Re: SQL Date Math

Ok - hit send to early!

2nd way;
When Dec( digits(Dec(year(curdate()),4,0)) ||
digits(Dec(month(curdate()),2,0)) ||
digits(Dec(day(curdate()),2,0)) ) = refdta

This extracts the year, month and day from current date, then puts them
back together and makes it into a number to compare with the field
refdta.

HTH

Jim


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.