RE: Problem selecting records in SQL using theSQL command DATE

[Alan Shore <ashore@xxxxxxxx>]

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.