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.
midrange.com
