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-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jim Essinger
Sent: Thursday, November 15, 2007 2:47 PM
To: Midrange Systems Technical Discussion
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
On Nov 15, 2007 1:33 PM, Jim Essinger <dilbernator@xxxxxxxxx> wrote:

Don,

2 ways -

1st - I use the UDF function iDate (by Alan Campin available free at
www.think400.dk/downloads.htm - Thanks Alan!) to translate the data
field into a date, then compare with the current date.

when iDate(refdta) = curdate()

Nice clean and easy.

When digits(year(curdate))



On Nov 15, 2007 12:11 PM, Don Cavaiani < dcavaiani@xxxxxxxxxxxxx>

wrote:

In System i5 Query (WRKQRY) I do the following:
------------------------------------------------
Result fields:

Today current(date)
Todaychar char(today)
Today2 '20' || substr(todaychar,7,2) || substr(todaychar,1,2 ||
substr(todaychar, 4,2) Refdta digits(refdt)
-------------------------------------------------
Then:

In Select Test:

refdta LE today2
--------------------------------
How would I do the same in SQL?

--
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.