Re: An easy (I think) SQL question

Hi Bill

Packed fields are not date fields - the expression you provide works when comparing against a true date field.

Your field looks like a CYYMMDD format - century- year-month-day

You might do well to take the date result you have and convert THAT to the numeric format - some combination of CHAR and DEC, perhaps - I can't drum anything up on short notice. Others shall, I'm sure.

Reason why this approach might be better is, there is no formula against a column of the table - THAT technique often results in a full table scan - very ineffiecient.

Vern

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

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