RE: Problem selecting records in SQL using theSQL command DATE

Thanks for your reply Jeff
That's what I thought at first, but the sql

select fieldmmdd, fieldyy,
DATE(substr(digits(fieldmmdd), 1, 2) concat '/' concat
substr(digits(fieldmmdd), 3, 2) concat '/' concat from FILEA
DID display data
Fieldmmdd fieldyy date
830 88 08/30/88
621 89 06/21/89
226 94 02/26/94
226 94 02/26/94
307 94 03/07/94
308 94 03/08/94
314 94 03/14/94
314 94 03/14/94
314 94 03/14/94
314 94 03/14/94
314 94 03/14/94
315 94 03/15/94
316 94 03/16/94
316 94 03/16/94
Etc
I checked STRSQL session attributes (F13 in STRSQL option 1) and the date format is *MDY

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 Jeff Young
Sent: Wednesday, August 17, 2011 1:11 PM
To: Midrange Systems Technical Discussion
Subject: Re: Problem selecting records in SQL using theSQL command DATE

Alan,
I believe that the DATE function is expecting a formatted ISO date.
You are passing a fomatted MDY date.




On Wed, Aug 17, 2011 at 12:29 PM, Alan Shore <ashore@xxxxxxxx> wrote:

I have a file that contains 2 separate numeric fields, that when
combined create a 6 digit date in MMDDYY format (NOT my idea - it is
what it is) The fields are
Fieldmmdd 4 0
Fieldyy 2 0

When I use the following SQL command
select * from FILEA where
DATE(substr(digits(fieldmmdd), 1, 2) concat '/' concat
substr(digits(fieldmmdd), 3, 2) concat '/' concat
digits(fieldyy)) > current_date - 3 months this results in the
following

Selection error involving field FIELDMMDD.

I then tried the following
select * from FILEA where
substr(digits(fieldmmdd), 1, 2) between '01' and '12' and
DATE(substr(digits(fieldmmdd), 1, 2) concat '/' concat
substr(digits(fieldmmdd), 3, 2) concat '/' concat
digits(fieldyy)) > current_date - 3 months with the same result

My thinking is that the data results in an invalid date.
How do I find those records with the invalid date?
I tried
select * from FILEA
DATE(substr(digits(fieldmmdd), 1, 2) concat '/' concat
substr(digits(fieldmmdd), 3, 2) concat '/' concat
digits(fieldyy)) is null
with the same result
Selection error involving field FIELDMMDD

As always, all replies gratefully accepted Please note I did try the
following
select fieldmmdd, fieldyy,
DATE(substr(digits(fieldmmdd), 1, 2) concat '/' concat
substr(digits(fieldmmdd), 3, 2) concat '/' concat from FILEA which
DID display data, but as the file is LARGE (couple of hundred
million), I'm pretty sure the display of data will help


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

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

--
Jeff Young
Sr. Programmer Analyst
--
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.