Re: SQL Date Math

["Jim Essinger" <dilbernator@xxxxxxxxx>]

Don,

2 ways -

1st - I use the UDF function iDate (by Alan Campin available free at
www.think400.dk/downloads.htm  - Thanks Alan!)  to tranlate 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?
>
>
>
>
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
> Sent: Wednesday, November 14, 2007 10:27 AM
> To: Midrange Systems Technical Discussion
> Subject: Re: SQL Date Math
>
> One last time trial...
> Instead of something like
> select * from ...
> where myNbrDate>CvtDateToNumISO(...)
> Try:
> select * from ...
> where CvtNumISOToDate(myNbrDate)>...
> Which will really tell us why you hose up the index by converting the
> file data to the input data instead of converting the input data to the
> file data.
>
> Rob Berendt
> --
> Group Dekko Services, LLC
> Dept 01.073
> PO Box 2000
> Dock 108
> 6928N 400E
> Kendallville, IN 46755
> http://www.dekko.com
>
>
>
>
>
> "Michael Ryan" <michaelrtr@xxxxxxxxx>
> Sent by: midrange-l-bounces@xxxxxxxxxxxx
> 11/14/2007 08:30 AM
> Please respond to
> Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
>
>
> To
> "Midrange Systems Technical Discussion" < midrange-l@xxxxxxxxxxxx>
> cc
>
> Fax to
>
> Subject
> Re: SQL Date Math
>
>
>
>
>
>
> That was interesting. Here are the results.
>
> All run in interactive SQL, approximately 56M records. Index built
> over the numeric field (which took a freaking long time btw - like 10
> minutes).
>
> Using CvtDateToNumISO function with no index - 45 seconds
> Using function code with no index - 38 seconds
> Using CvtDateToNumISO function with index - 11 seconds
> Using function code with index - 2 seconds
>
> On Nov 13, 2007 7:05 PM, Michael Ryan <michaelrtr@xxxxxxxxx> wrote:
> > Yes...going from date to numeric. I just named it backwards to throw
> > off the opposition. :)
> >
> > Sure...I'll give it a go first thing in the morning, and I'll post my
> > results back here. I'll also create an index over the numeric field
> > and see how that helps things.
> >
> > On Nov 13, 2007 5:28 PM, Elvis Budimlic
> >
> > <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
> > > Michael,
> > >
> > > Do you mean CvtDateToNumISO?  Going from DATE to NUMERIC, not the
> other way
> > > around.
> > >
> > > If so, do you mind running another test?
> > >
> > > Take Charles' code out of the UDF and put it in the SQL statement
> itself.
> > > Then run it.
> > > I'm curious to see if UDF call itself contributes any significant
> overhead.
> > > BTW, do you have an index over the numeric(8,0) field?
> > > If not, I think it would be worth running a test again after you
> create one.
> > > 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
> > >
> > > Charles' CvtNumtoDateISO function works swell! It takes 120 seconds
> > > for my query (56M records) using the date and substr method, and 39
> > > seconds for the CvtNumToDateISO method. And that's on the
> development
> > > system. Much faster on the production system.
> > >
> > >
> > >
> > >
> > > --
> > >
> > > 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.
> --
> 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.
>
>
> --
> 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.
>
>
> --
> 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.