Re: An easy (I think) SQL question

Best way...

Download & restore Alan Campin's iDate
http://www.think400.dk/downloads.htm

Now you have a set of SQL User Defined Functions available to convert from
numeric date to a date type and vice versa.

You could then do
select *
from mytbl
where idate(numdate,'*CYMD')
between (CURRENT_DATE - (DAY(CURRENT_DATE)-1) DAYS - 3 MONTH)
and (CURRENT_DATE - (DAY(CURRENT_DATE)-1) DAYS )

However, it's probably better to do it this way
select *
from mytbl
where numdate
between ConvertToIdate(CURRENT_DATE - (DAY(CURRENT_DATE)-1) DAYS -
3 MONTH), '*CYMD')
and ConvertToiDate(CURRENT_DATE -
(DAY(CURRENT_DATE)-1) DAYS, '*CYMD')

That allows existing indexes on numdate to be used.





On Tue, Jun 28, 2016 at 4:50 PM, Bill Howie <blhowie66@xxxxxxxxx> wrote:

Hello all,

I have what I think is a fairly simple SQL question but it's baffling me.
I have a field in a file that is a date (field is in packed format). The
date in the field looks like this: 1160628. The need is to only grab
records from this file that go back 3 months from the first day of the
current month, so in this case I'd only want records with a date greater
than 1160301. This also needs to work if when subtracting the 3 months I
had to go across multiple years. I know I can do a formula something like
this:

(CURRENT_DATE - (DAY(CURRENT_DATE)-1) DAYS - 3 MONTH)


But I'm not sure how to use the packed numeric date field I have to get
there. Any thoughts would be greatly appreciated. Thanks!


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

Please contact support@xxxxxxxxxxxx for any subscription related
questions.