Re: An easy (I think) SQL question

I would replace the CHAR in the example below with DIGITS.

Char drops leading zeroes giving an error.

Digits preserves the leading zeroes.

As a check on date validity, I usually check that the month is between 1 and 12. If that fails, I know the date is bad and I replace it with '1900-01-01'.

You could add other validations to day and year.

Darryl Freinkel
iPad

On Jun 28, 2016, at 5:17 PM, Buck Calabro <kc2hiz@xxxxxxxxx> wrote:

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.

--
--buck

Visit wiki.midrange.com and register for an account. Edit a page that
helps you, and because it's public, you'll help someone else, too.

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