Re: SQL Date Conversion Statement Error

Mike,

Sometime ago Birgitta Hauser posted a nice code for converting numeric
fields to date. It was something like this:

Char(Date(Digits(YYYYMMDD) CONCAT '000000'), ISO)

At the very least (supposing that all your date values are correct, it will
provide for simpler code and make it easier to debug...

HTH,

Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries
--



On Wed, Oct 26, 2011 at 11:50 AM, Mike Wills <mike@xxxxxxxxxxxx> wrote:

I have a field that is a decimal date (YYYYMMDD). I need to find all
records that are a year or less old. Since date are so easy, I am
trying to do:

WHERE DATE(SUBSTR(CHAR(A.CPADDT),1,4) CONCAT '-' CONCAT
SUBSTR(CHAR(A.CPADDT),5,2) CONCAT '-' CONCAT
SUBSTR(CHAR(A.CPADDT),7,2)) > CURRENT_DATE - 1 YEAR

This works great in the SELECT portion. When I put it in the WHERE
section I get "Selection error involving field *N."

I am really started to hate decimal dates just as much as our other
developer hates date fields.

Any ideas of what I am doing wrong?

--
Mike Wills
http://mikewills.me
--
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.