Re: An easy (I think) SQL question

Hi Bill,

In addition to the other methods people have responded with, here is an
alternative using only IBM's functions if you prefer.

with CTE_MY_DATE ( MY_DATE ) as ( values dec( 1160628, 7, 0) )

select date( timestamp_format( case when substr( digits( MY_DATE ), 1, 1 )
= '1' then '20' else '19' end concat substr( digits( MY_DATE ), 2 ),
'YYYYMMDD' ) ) as REAL_DATE
from CTE_MY_DATE

Using the RR format string instead of YYYY is another alternative (see the
TIMESTAMP_FORMAT function documentation).

Mike

date: Tue, 28 Jun 2016 16:50:46 -0400

from: Bill Howie <blhowie66@xxxxxxxxx>
subject: An easy (I think) SQL question

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