Re: Problem selecting records in SQL using theSQL command DATE

[Jack Kingsley <iseriesflorida@xxxxxxxxx>]

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.