Re: SQL Date Conversion Statement Error

[Mike Wills <mike@xxxxxxxxxxxx>]

Your right. There was "zero dates" where there isn't supposed to be.
After I removed those from the query it works.

A.CPADDT <> 0 AND
DATE(SUBSTR(DIGITS(A.CPADDT),1,4) CONCAT '-' CONCAT
SUBSTR(DIGITS(A.CPADDT),5,2) CONCAT '-' CONCAT
SUBSTR(DIGITS(A.CPADDT),7,2)) > CURRENT_DATE - 1 YEAR

In this particular case, we are only including the data for self
look-up if a person wants to see the status of their payment, I'll
just ignore those.

--
Mike Wills
http://mikewills.me



On Wed, Oct 26, 2011 at 11:51 AM, Alan Shore <ashore@xxxxxxxx> wrote:
> Hi Mike
> I had almost the EXACT same problem
> It turned out to be that one or more records held data in the decimal field that were in fact invalid dates (20110231 for example)
> I posted my grievances here and the resounding answer was to load and use the function idate (from Alan Campin)
> Here is the web site
>  http://www.think400.dk/downloads.htm
>
> I have done so, loading it to all of our systems and have NOT regretted it since
>
> Alan Shore
> Programmer/Analyst, Direct Response
> E:AShore@xxxxxxxx
> P:(631) 200-5019
> C:(631) 880-8640
> "If you're going through Hell, keep going" - Winston Churchill
>
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Mike Wills
> Sent: Wednesday, October 26, 2011 12:21 PM
> To: midrange-l@xxxxxxxxxxxx
> Subject: SQL Date Conversion Statement Error
>
> 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.
>
> --
> 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.