RE: Problem selecting records in SQL using theSQL command DATE

Using the following sql
select
digits(fieldmmdd),
digits(fieldyy) from FILEA
The following is displayed
DIGITS ( FIELDMMDD ) DIGITS ( FIELDYY )
0830 88
0621 89
0226 94
0226 94
0307 94
0308 94
0314 94
0314 94
0314 94
0314 94
0314 94
0315 94
0316 94
0316 94
0316 94
0317 94
Etc.
Etc.
Etc.



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

What does the data look like via query or sql.

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.

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