Re: An easy (I think) SQL question

On 6/28/2016 4:50 PM, Bill Howie wrote:

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!

The thing you're thinking of as a date is really a packed number. This
is more than semantic nit picking. These SQL date operations will only
work on true dates. So... you need to convert the packed number to a
date. I like Alan Campin's IDATE for this
http://www.think400.dk/downloads.htm

But if you want to roll your own, you need to convert the pack to char,
then substring out all the pieces, assemble them with ISO editing and
perform a DATE() over that. I have an RPG sub-procedure that does all
of that, similar to IDATE.

I have a tiny test file I use for testing this stuff; it has a field
called BINARY which holds dates in CYYMMDD form. Here is an SQL
statement that looks like it'll do the conversion:

select binary,
date(
case
when substr(char(binary), 1, 1) = '1' then '20'
else '19'
end concat
substr(char(binary), 2, 2) concat '-' concat
substr(char(binary), 4, 2) concat '-' concat
substr(char(binary), 6, 2)
) as truedate
from buck.datesample
where binary <> 0;

If the input is 1160628, the output is 2016-06-28

It may be easiest to read from the inside, out.