RE: Problem selecting records in SQL using theSQL command DATE

Thanks for your reply Rob
I have been trying to get this function onto our system for a while, but time has always been of the essence etc. etc.
I have just been given the procedure to get through the Firewall and have loaded the idate save file onto our development AS400
I'll follow the instruction and post my results
This while I am trying to get 5 projects ready for tonight's production run


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 rob@xxxxxxxxx
Sent: Wednesday, August 17, 2011 4:31 PM
To: Midrange Systems Technical Discussion
Subject: RE: Problem selecting records in SQL using theSQL command DATE

If they will not let you load free software then mimic it.
A sample user defined function may be

STRSQL

CREATE FUNCTION
BEGIN DECLARE CHARNUMDATE CHAR ( 8 ) ;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN RETURN NULL ; END ; SET CHARNUMDATE = CHAR ( NUMDATE ) ; RETURN DATE ( SUBSTR ( CHARNUMDATE , 1 , 4 ) || '-' || SUBSTR ( CHARNUMDATE , 5 , 2 ) || '-' || SUBSTR ( CHARNUMDATE , 7 , 2 ) ) ; END

Then you end up selecting the rows with a null return value to find the erroneous ones like Alan's example.

Rob Berendt
--
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: Alan Shore <ashore@xxxxxxxx>
To: "'Midrange Systems Technical Discussion'"
<midrange-l@xxxxxxxxxxxx>
Date: 08/17/2011 01:23 PM
Subject: RE: Problem selecting records in SQL using theSQL command
DATE
Sent by: midrange-l-bounces@xxxxxxxxxxxx



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.