× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.