Re: SQL Date Conversion Statement Error

[Luis Rodriguez <luisro58@xxxxxxxxx>]

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.