RE: Problem selecting records in SQL using theSQL command DATE

Thanks for the reply Alan
Unfortunately no
This may be the reason why my bosses will now give me the time and authority to load it, but I was hoping for something that I could use now

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

You don't have iDate?

SELECT * FROM FILEA
Where iDate((Fieldmmdd * 100) + 11,'*MDY') is null

On Wed, Aug 17, 2011 at 10:29 AM, 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.