Re: An easy (I think) SQL question

"MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx> wrote on 06/28/2016
04:50:46 PM:

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!

You can convert your packed-decimal field to a date as follows.
What this does is change your CYYMMDD field to a YYYYMMDD value before
rendering it as character data for input to the TIMESTAMP_FORMAT()
function and then into the DATE() function specifying the 'YYYYMMDD'
format option.

DATE(TIMESTAMP_FORMAT(CHAR(myfield+19000000), 'YYYYMMDD'))

Naturally, it would be better to do this when modernizing the file
into a new SQL table.

Sincerely,

Dave Clark