Testing for Invalid Dates

Lockwood Lyon

Testing for Invalid Dates
Esteemed List:

Okay, I've forgotten this (again). I have a character column which
'usually' contains a valid date. If, however, the date is invalid (e.g.,
spaces) I wish to use Current Date. I tried:

SELECT DATE('11/31/2007')
FROM SYSIBM.SYSDUMMY1

But instead of Null I get an SQLCode -181 (bad datetime value). I had
been hoping to code something like:

SELECT IFNULL ( DATE('11/31/2007') , CURRENT DATE )
FROM SYSIBM.SYSDUMMY1

but, clearly that won't work.

Any ideas or pointers to RFTMs ?

Thanks in advance!

- Lock Lyon
Principal DBA
Fifth Third Bancorp


This e-mail transmission contains information that is confidential and may be privileged. It is intended only for the addressee(s) named above. If you receive this e-mail in error, please do not read, copy or disseminate it in any manner. If you are not the intended recipient, any disclosure, copying, distribution or use of the contents of this information is prohibited. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please erase it from your computer system. Your assistance in correcting this error is appreciated.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Walter Trovijo Jr (UOL)

Re: Testing for Invalid Dates
(in response to Lockwood Lyon)
Hard to say without knowing what you're trying to do. Best way is to define it as a date column.
If there's some kind of application/environment compatibility issues with date columns, I'd say that application should take the responsibility of assuring valid AND correct dates are used when inserting/updating. Also, it does not make much sense to me - again, I'm trying to figure out the whole picture - to set current date instead of a invalid date; let's say you're trying to insert some kind of expiration date to be two years from now, date is wrong you then set expiration date to today...

If table is populated already and you just want to fix data periodically, I would suggest a program to select using date function from the column and handling -181 to fix invalid dates and trying to interpret and set date values as precisely as possible.

HTH,
Walter Trovijo Jr.





---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Lockwood Lyon

Re: Testing for Invalid Dates
(in response to Walter Trovijo Jr (UOL))
I greatly simplified my example in order to protect the developers /
database designers.

Here's the question, re-stated: "How, in an SQL statement, can I
determine if a CHAR(8) column contains a vaild date?".

- Lock Lyon
Principal DBA
Fifth Third Bancorp

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of wtrovijo
Sent: Friday, January 19, 2007 8:50 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Testing for Invalid Dates

Hard to say without knowing what you're trying to do. Best way is to
define it as a date column.
If there's some kind of application/environment compatibility issues
with date columns, I'd say that application should take the
responsibility of assuring valid AND correct dates are used when
inserting/updating. Also, it does not make much sense to me - again, I'm
trying to figure out the whole picture - to set current date instead of
a invalid date; let's say you're trying to insert some kind of
expiration date to be two years from now, date is wrong you then set
expiration date to today...

If table is populated already and you just want to fix data
periodically, I would suggest a program to select using date function
from the column and handling -181 to fix invalid dates and trying to
interpret and set date values as precisely as possible.

HTH,
Walter Trovijo Jr.


This e-mail transmission contains information that is confidential and may be privileged. It is intended only for the addressee(s) named above. If you receive this e-mail in error, please do not read, copy or disseminate it in any manner. If you are not the intended recipient, any disclosure, copying, distribution or use of the contents of this information is prohibited. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please erase it from your computer system. Your assistance in correcting this error is appreciated.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Walter Trovijo Jr (UOL)

Re: Testing for Invalid Dates
(in response to Lockwood Lyon)
So the short answer, imho, is to handle -181.

> I greatly simplified my example in order to protect the developers /
> database designers.
>
> Here's the question, re-stated: "How, in an SQL statement, can I
> determine if a CHAR(8) column contains a vaild date?".
>
> - Lock Lyon
> Principal DBA
> Fifth Third Bancorp
>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Brian Goldberg

Re: Testing for Invalid Dates
(in response to Walter Trovijo Jr (UOL))
You could write a CASE statement to parse the string for values in a range:

case
when substr(test_date_char, 1, 4) > '1900'
and substr(test_date_char, 1, 4) < '2100'
and substr(test_date_char, 5, 2) > '00'
and substr(test_date_char, 5, 2) < '13'
and substr(test_date_char, 7, 2) > '00'
and substr(test_date_char, 7, 2) < '32'
then test_date_char
else 'invalid date'
end as valid_date



wtrovijo
<[login to unmask email]
.BR> To
Ext: [login to unmask email]
Sent by: DB2 Data cc
Base Discussion
List Subject
<[login to unmask email] Re: [DB2-L] Testing for Invalid
ORG> Dates


01/19/2007 09:21
AM


Please respond to
DB2 Database
Discussion list
at IDUG
<[login to unmask email]
2-L.ORG>






So the short answer, imho, is to handle -181.

> I greatly simplified my example in order to protect the developers /
> database designers.
>
> Here's the question, re-stated: "How, in an SQL statement, can I
> determine if a CHAR(8) column contains a vaild date?".
>
> - Lock Lyon
> Principal DBA
> Fifth Third Bancorp
>

---------------------------------------------------------------------------------

Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org
. The IDUG List Admins can be reached at [login to unmask email]
Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Lockwood Lyon

Re: Testing for Invalid Dates
(in response to Brian Goldberg)
Well, with this solution I think you'd also need to consider months with
< 31 days, and February in leap year. While I certainly could code such
a statement, I was hoping for a simple function (or set of functions)
that would return NULL (or something) if the conversion of the string
failed.

Another Lister suggested handling the SQLCode -181. I don't think this
is possible within an SQL statement, and would love to see an example.

If we move 'outside' SQL for the answer, then there are already several
non-SQL ways of handling an invalid date. We already know of these;
it's the "within a single SQL statement" that we were wondering about.

- Lock Lyon
Principal DBA
First Third Bancorp

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of [login to unmask email]
Sent: Friday, January 19, 2007 9:43 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Testing for Invalid Dates

You could write a CASE statement to parse the string for values in a
range:

case
when substr(test_date_char, 1, 4) > '1900'
and substr(test_date_char, 1, 4) < '2100'
and substr(test_date_char, 5, 2) > '00'
and substr(test_date_char, 5, 2) < '13'
and substr(test_date_char, 7, 2) > '00'
and substr(test_date_char, 7, 2) < '32'
then test_date_char
else 'invalid date'
end as valid_date




wtrovijo

<[login to unmask email]

.BR>
To
Ext: [login to unmask email]

Sent by: DB2 Data
cc
Base Discussion

List
Subject
<[login to unmask email] Re: [DB2-L] Testing for Invalid

ORG> Dates





01/19/2007 09:21

AM





Please respond to

DB2 Database

Discussion list

at IDUG

<[login to unmask email]

2-L.ORG>









So the short answer, imho, is to handle -181.

> I greatly simplified my example in order to protect the developers /
> database designers.
>
> Here's the question, re-stated: "How, in an SQL statement, can I
> determine if a CHAR(8) column contains a vaild date?".
>
> - Lock Lyon
> Principal DBA
> Fifth Third Bancorp
>



This e-mail transmission contains information that is confidential and may be privileged. It is intended only for the addressee(s) named above. If you receive this e-mail in error, please do not read, copy or disseminate it in any manner. If you are not the intended recipient, any disclosure, copying, distribution or use of the contents of this information is prohibited. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please erase it from your computer system. Your assistance in correcting this error is appreciated.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Mike Hutchinson

Re: Testing for Invalid Dates
(in response to Lockwood Lyon)
Lockwood:
I'm guessing you might be able to set-up a LOCAL non-default DB2
exit which returns null for invalid values, and then code CHAR(HIREDATE,
LOCAL) during the retrieval. See the dsnagj13.pdf appendices.

Mike Hutchinson
Sr Applications DBA
Jackson National Life




"Lyon, Lockwood" <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
01/19/2007 09:53 AM
Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>


To
[login to unmask email]
cc

Subject
Re: [DB2-L] Testing for Invalid Dates






Well, with this solution I think you'd also need to consider months with
< 31 days, and February in leap year. While I certainly could code such
a statement, I was hoping for a simple function (or set of functions)
that would return NULL (or something) if the conversion of the string
failed.

Another Lister suggested handling the SQLCode -181. I don't think this
is possible within an SQL statement, and would love to see an example.

If we move 'outside' SQL for the answer, then there are already several
non-SQL ways of handling an invalid date. We already know of these;
it's the "within a single SQL statement" that we were wondering about.

- Lock Lyon
Principal DBA
First Third Bancorp

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of [login to unmask email]
Sent: Friday, January 19, 2007 9:43 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Testing for Invalid Dates

You could write a CASE statement to parse the string for values in a
range:

case
when substr(test_date_char, 1, 4) > '1900'
and substr(test_date_char, 1, 4) < '2100'
and substr(test_date_char, 5, 2) > '00'
and substr(test_date_char, 5, 2) < '13'
and substr(test_date_char, 7, 2) > '00'
and substr(test_date_char, 7, 2) < '32'
then test_date_char
else 'invalid date'
end as valid_date




wtrovijo

<[login to unmask email]

.BR>
To
Ext: [login to unmask email]

Sent by: DB2 Data
cc
Base Discussion

List
Subject
<[login to unmask email] Re: [DB2-L] Testing for Invalid

ORG> Dates





01/19/2007 09:21

AM





Please respond to

DB2 Database

Discussion list

at IDUG

<[login to unmask email]

2-L.ORG>









So the short answer, imho, is to handle -181.

> I greatly simplified my example in order to protect the developers /
> database designers.
>
> Here's the question, re-stated: "How, in an SQL statement, can I
> determine if a CHAR(8) column contains a vaild date?".
>
> - Lock Lyon
> Principal DBA
> Fifth Third Bancorp
>



This e-mail transmission contains information that is confidential and may
be privileged. It is intended only for the addressee(s) named above. If
you receive this e-mail in error, please do not read, copy or disseminate
it in any manner. If you are not the intended recipient, any disclosure,
copying, distribution or use of the contents of this information is
prohibited. Please reply to the message immediately by informing the
sender that the message was misdirected. After replying, please erase it
from your computer system. Your assistance in correcting this error is
appreciated.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Mike Hutchinson

Re: Testing for Invalid Dates
(in response to Mike Hutchinson)
But a UDF returning NULL when the date is invalid would have less
system-wide impact. At least it's simple to use once it's coded.

Mike Hutchinson
Sr Applications DBA
517-367-3488 (x23488)
Jackson National Life



"Lyon, Lockwood" <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
01/19/2007 09:53 AM
Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>


To
[login to unmask email]
cc

Subject
Re: [DB2-L] Testing for Invalid Dates






Well, with this solution I think you'd also need to consider months with
< 31 days, and February in leap year. While I certainly could code such
a statement, I was hoping for a simple function (or set of functions)
that would return NULL (or something) if the conversion of the string
failed.

Another Lister suggested handling the SQLCode -181. I don't think this
is possible within an SQL statement, and would love to see an example.

If we move 'outside' SQL for the answer, then there are already several
non-SQL ways of handling an invalid date. We already know of these;
it's the "within a single SQL statement" that we were wondering about.

- Lock Lyon
Principal DBA
First Third Bancorp

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of [login to unmask email]
Sent: Friday, January 19, 2007 9:43 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Testing for Invalid Dates

You could write a CASE statement to parse the string for values in a
range:

case
when substr(test_date_char, 1, 4) > '1900'
and substr(test_date_char, 1, 4) < '2100'
and substr(test_date_char, 5, 2) > '00'
and substr(test_date_char, 5, 2) < '13'
and substr(test_date_char, 7, 2) > '00'
and substr(test_date_char, 7, 2) < '32'
then test_date_char
else 'invalid date'
end as valid_date




wtrovijo

<[login to unmask email]

.BR>
To
Ext: [login to unmask email]

Sent by: DB2 Data
cc
Base Discussion

List
Subject
<[login to unmask email] Re: [DB2-L] Testing for Invalid

ORG> Dates





01/19/2007 09:21

AM





Please respond to

DB2 Database

Discussion list

at IDUG

<[login to unmask email]

2-L.ORG>









So the short answer, imho, is to handle -181.

> I greatly simplified my example in order to protect the developers /
> database designers.
>
> Here's the question, re-stated: "How, in an SQL statement, can I
> determine if a CHAR(8) column contains a vaild date?".
>
> - Lock Lyon
> Principal DBA
> Fifth Third Bancorp
>



This e-mail transmission contains information that is confidential and may
be privileged. It is intended only for the addressee(s) named above. If
you receive this e-mail in error, please do not read, copy or disseminate
it in any manner. If you are not the intended recipient, any disclosure,
copying, distribution or use of the contents of this information is
prohibited. Please reply to the message immediately by informing the
sender that the message was misdirected. After replying, please erase it
from your computer system. Your assistance in correcting this error is
appreciated.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Martin Kenney

Re: Testing for Invalid Dates
(in response to Mike Hutchinson)
Marty Kenney
Sr. DB2 DBA
(919) 651-5211

The following SQL does not check for invalid years, and I was much to
lazy to do leap year checking, but other than those two scenerios it
should
do what you want (date entered if valid, current date if not valid).

SELECT CASE WHEN INT(SUBSTR('04/31/2007',1,2)) = 1

THEN CASE WHEN INT(SUBSTR('04/31/2007',4,2)) < 1

OR INT(SUBSTR('04/31/2007',4,2)) > 31

THEN CHAR(CURRENT DATE)

ELSE '04/31/2007' END

WHEN INT(SUBSTR('04/31/2007',1,2)) = 2

THEN CASE WHEN INT(SUBSTR('04/31/2007',4,2)) < 1

OR INT(SUBSTR('04/31/2007',4,2)) > 28

THEN CHAR(CURRENT DATE)

ELSE '04/31/2007' END

WHEN INT(SUBSTR('04/31/2007',1,2)) = 3

THEN CASE WHEN INT(SUBSTR('04/31/2007',4,2)) < 1

OR INT(SUBSTR('04/31/2007',4,2)) > 31

THEN CHAR(CURRENT DATE)

ELSE '04/31/2007' END

WHEN INT(SUBSTR('04/31/2007',1,2)) = 4

THEN CASE WHEN INT(SUBSTR('04/31/2007',4,2)) < 1

OR INT(SUBSTR('04/31/2007',4,2)) > 30

THEN CHAR(CURRENT DATE)

ELSE '04/31/2007' END

WHEN INT(SUBSTR('04/31/2007',1,2)) = 5

THEN CASE WHEN INT(SUBSTR('04/31/2007',4,2)) < 1

OR INT(SUBSTR('04/31/2007',4,2)) > 31

THEN CHAR(CURRENT DATE)

ELSE '04/31/2007' END

WHEN INT(SUBSTR('04/31/2007',1,2)) = 6

THEN CASE WHEN INT(SUBSTR('04/31/2007',4,2)) < 1

OR INT(SUBSTR('04/31/2007',4,2)) > 30

THEN CHAR(CURRENT DATE)

ELSE '04/31/2007' END

WHEN INT(SUBSTR('04/31/2007',1,2)) = 7

THEN CASE WHEN INT(SUBSTR('04/31/2007',4,2)) < 1

OR INT(SUBSTR('04/31/2007',4,2)) > 31

THEN CHAR(CURRENT DATE)

ELSE '04/31/2007' END

WHEN INT(SUBSTR('04/31/2007',1,2)) = 8

THEN CASE WHEN INT(SUBSTR('04/31/2007',4,2)) < 1

OR INT(SUBSTR('04/31/2007',4,2)) > 31

THEN CHAR(CURRENT DATE)

ELSE '04/31/2007' END

WHEN INT(SUBSTR('04/31/2007',1,2)) = 9

THEN CASE WHEN INT(SUBSTR('04/31/2007',4,2)) < 1

OR INT(SUBSTR('04/31/2007',4,2)) > 30

THEN CHAR(CURRENT DATE)

ELSE '04/31/2007' END

WHEN INT(SUBSTR('04/31/2007',1,2)) = 10

THEN CASE WHEN INT(SUBSTR('04/31/2007',4,2)) < 1

OR INT(SUBSTR('04/31/2007',4,2)) > 31

THEN CHAR(CURRENT DATE)

ELSE '04/31/2007' END

WHEN INT(SUBSTR('04/31/2007',1,2)) = 11

THEN CASE WHEN INT(SUBSTR('04/31/2007',4,2)) < 1

OR INT(SUBSTR('04/31/2007',4,2)) > 30

THEN CHAR(CURRENT DATE)

ELSE '04/31/2007' END

WHEN INT(SUBSTR('04/31/2007',1,2)) = 12

THEN CASE WHEN INT(SUBSTR('04/31/2007',4,2)) < 1

OR INT(SUBSTR('04/31/2007',4,2)) > 31

THEN CHAR(CURRENT DATE)

ELSE '04/31/2007' END

ELSE CHAR(CURRENT DATE)

END

FROM SYSIBM.SYSDUMMY1;

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Philip Sevetson

Re: Testing for Invalid Dates
(in response to Martin Kenney)
Marty,

This is good work. You obviously have too much time on your hands, or a
sick and twisted obsession with getting SQL to do everything.

--Phil


On 1/19/07, Kenney, Marty <[login to unmask email]> wrote:
>
> Marty Kenney
> Sr. DB2 DBA
> (919) 651-5211
>
> The following SQL does not check for invalid years, and I was much to
> lazy to do leap year checking, but other than those two scenerios it
> should
> do what you want (date entered if valid, current date if not valid).
>
> SELECT CASE WHEN INT(SUBSTR('04/31/2007',1,2)) = 1
> THEN CASE WHEN INT(SUBSTR('04/31/2007',4,2)) < 1
> OR INT(SUBSTR('04/31/2007',4,2)) > 31
> THEN CHAR(CURRENT DATE)
> ELSE '04/31/2007' END
> WHEN INT(SUBSTR('04/31/2007',1,2)) = 2
> THEN CASE WHEN INT(SUBSTR('04/31/2007',4,2)) < 1
> OR INT(SUBSTR('04/31/2007',4,2)) > 28
> THEN CHAR(CURRENT DATE)
> ELSE '04/31/2007' END
> WHEN INT(SUBSTR('04/31/2007',1,2)) = 3
> THEN CASE WHEN INT(SUBSTR('04/31/2007',4,2)) < 1
> OR INT(SUBSTR('04/31/2007',4,2)) > 31
> THEN CHAR(CURRENT DATE)
> ELSE '04/31/2007' END
> WHEN INT(SUBSTR('04/31/2007',1,2)) = 4
> THEN CASE WHEN INT(SUBSTR('04/31/2007',4,2)) < 1
> OR INT(SUBSTR('04/31/2007',4,2)) > 30
> THEN CHAR(CURRENT DATE)
> ELSE '04/31/2007' END
> WHEN INT(SUBSTR('04/31/2007',1,2)) = 5
> THEN CASE WHEN INT(SUBSTR('04/31/2007',4,2)) < 1
> OR INT(SUBSTR('04/31/2007',4,2)) > 31
> THEN CHAR(CURRENT DATE)
> ELSE '04/31/2007' END
> WHEN INT(SUBSTR('04/31/2007',1,2)) = 6
> THEN CASE WHEN INT(SUBSTR('04/31/2007',4,2)) < 1
> OR INT(SUBSTR('04/31/2007',4,2)) > 30
> THEN CHAR(CURRENT DATE)
> ELSE '04/31/2007' END
> WHEN INT(SUBSTR('04/31/2007',1,2)) = 7
> THEN CASE WHEN INT(SUBSTR('04/31/2007',4,2)) < 1
> OR INT(SUBSTR('04/31/2007',4,2)) > 31
> THEN CHAR(CURRENT DATE)
> ELSE '04/31/2007' END
> WHEN INT(SUBSTR('04/31/2007',1,2)) = 8
> THEN CASE WHEN INT(SUBSTR('04/31/2007',4,2)) < 1
> OR INT(SUBSTR('04/31/2007',4,2)) > 31
> THEN CHAR(CURRENT DATE)
> ELSE '04/31/2007' END
> WHEN INT(SUBSTR('04/31/2007',1,2)) = 9
> THEN CASE WHEN INT(SUBSTR('04/31/2007',4,2)) < 1
> OR INT(SUBSTR('04/31/2007',4,2)) > 30
> THEN CHAR(CURRENT DATE)
> ELSE '04/31/2007' END
> WHEN INT(SUBSTR('04/31/2007',1,2)) = 10
> THEN CASE WHEN INT(SUBSTR('04/31/2007',4,2)) < 1
> OR INT(SUBSTR('04/31/2007',4,2)) > 31
> THEN CHAR(CURRENT DATE)
> ELSE '04/31/2007' END
> WHEN INT(SUBSTR('04/31/2007',1,2)) = 11
> THEN CASE WHEN INT(SUBSTR('04/31/2007',4,2)) < 1
> OR INT(SUBSTR('04/31/2007',4,2)) > 30
> THEN CHAR(CURRENT DATE)
> ELSE '04/31/2007' END
> WHEN INT(SUBSTR('04/31/2007',1,2)) = 12
> THEN CASE WHEN INT(SUBSTR('04/31/2007',4,2)) < 1
> OR INT(SUBSTR('04/31/2007',4,2)) > 31
> THEN CHAR(CURRENT DATE)
> ELSE '04/31/2007' END
> ELSE CHAR(CURRENT DATE)
> END
> FROM SYSIBM.SYSDUMMY1;
>
>
> ---------------------------------------------------------------------------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
> home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
> select "Join or Leave the list". The IDUG DB2-L FAQ is at
> http://www.idugdb2-l.org. The IDUG List Admins can be reached at
> [login to unmask email] Find out the latest on IDUG conferences
> at http://conferences.idug.org/index.cfm
>



--
--Phil Sevetson
IBM Certified Solutions Expert -- DB2 V7.1 Database Administration for
OS/390
IBM Certified Database Administrator -- DB2 Universal Database V8.1 for zOS
The Reserve
New York, NY, USA
ANY OPINIONS OR RECOMMENDATIONS EXPRESSED IN THIS EMAIL
ARE MY OWN, NOT MY EMPLOYER'S OR IBM'S.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Ava Collins

Re: Testing for Invalid Dates
(in response to Philip Sevetson)
Another convoluted way:
* 0000-PREPARE-DATE-CHECK.
* 1. DROPS AND DECLARES TEMPORARY TABLE TO VALIDATE INPUT
* DATE
***************************************************************
*
0000-PREPARE-DATE-CHECK.
*
EXEC SQL DROP TABLE SESSION.CKDT
END-EXEC.

EXEC SQL DECLARE GLOBAL TEMPORARY TABLE
SESSION.CKDT
( CKDT_TMSTMP TIMESTAMP NOT NULL
) END-EXEC.
*

IF SQLCODE NOT = 0
<snip>
PERFORM 9500-FORMAT-OUTMSG THRU
9500-FORMAT-OUTMSG-EXIT
PERFORM 9999-RETURN-ERROR THRU
9999-RETURN-ERROR-EXIT
END-IF.
*
0110-VALID-DATE.
MOVE ' ' TO WS-VALID-UPDDATE.
MOVE CHARDATE TO WS-UPDATE
*
PERFORM 185-VALIDATE-DATE THRU
185-VALIDATE-DATE-EXIT.
IF WS-VALID-UPDDATE NOT = 'Y'
MOVE '0110-VALID-DATE ' TO P-OUTMSG-PARG
<snip>
PERFORM 9999-RETURN-ERROR THRU
9999-RETURN-ERROR-EXIT
END-IF.

<snip>
185-VALIDATE-DATE.
MOVE SPACES TO WS-TMSTMP.
MOVE WS-UPDATE TO WS-TMSTMP-DATE.
MOVE '-00.00.00.000000' TO WS-TMSTMP-REST.

EXEC SQL
INSERT
INTO
SESSION.CKDT
(CKDT_TMSTMP)
VALUES
(:WS-TMSTMP)
END-EXEC.
IF SQLCODE NOT = 0
MOVE 'N' TO WS-VALID-UPDDATE
MOVE '185-VALIDATE-DATE ' TO P-OUTMSG-PARG
MOVE 'INSERT SESSION EWGTCKDT '
TO P-OUTMSG-ERR-MSG
MOVE -999 TO WS-OUTCODES-PARMS
ELSE
MOVE 'Y' TO WS-VALID-UPDDATE
END-IF.
185-VALIDATE-DATE-EXIT.

I warned you it was convoluted! Provided primarily for your entertaiinment. Not something I'd recommend due to the overhead of using the temp table.

Jacquie

________________________________________________________________________
Check out the new AOL. Most comprehensive set of free safety and security tools, free access to millions of high-quality videos from across the web, free AOL Mail and more.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Martin Kenney

Re: Testing for Invalid Dates
(in response to Ava Collins)
If a program will work for you then the very simple SQL stored procedure
works fine:



CREATE PROCEDURE AMPSPASD.NAK1

(INOUT DATE_INPUT CHAR(10))

FENCED

EXTERNAL NAME 'NAK1'

DYNAMIC RESULT SETS 0

LANGUAGE SQL

NOT DETERMINISTIC

READS SQL DATA

NO DBINFO

COLLID AMPSPASD

WLM ENVIRONMENT DB2BSTLD

ASUTIME NO LIMIT

STAY RESIDENT NO

SECURITY DB2

RUN OPTIONS 'MSGFILE(SYSOUT,FBA,121,0,ENQ)'

COMMIT ON RETURN NO

P1: BEGIN NOT ATOMIC

DECLARE CONTINUE HANDLER FOR SQLSTATE '22007'

SELECT CHAR(CURRENT DATE)

INTO DATE_INPUT FROM SYSIBM.SYSDUMMY1;

SELECT CHAR(DATE(DATE_INPUT))

INTO DATE_INPUT FROM SYSIBM.SYSDUMMY1;

END P1





Marty Kenney

Sr. DB2 DBA

(919) 651-5211



------------------------------------------------------------------------
--------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the
archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Richard Fazio

Re: Testing for Invalid Dates
(in response to Martin Kenney)
It seems to me a simple UDF is the answer.

Pass in your text field and the current date....in the code, if it's
valid, pass the text field back. If it's invalid, use the current date
field. This would always return back a single (and valid) date.

You could write some SQL to interpret the text, but the permutations to
cover would be significant (not to mention the overhead).

i.e.. If substr(text_col,1) >= 0 and <=9....

The overhead for a UDF call is about 0.0001 seconds. Unless you are
doing a billion or so, this may be good for you.
faz

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Lyon, Lockwood
Sent: Friday, January 19, 2007 8:07 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Testing for Invalid Dates

I greatly simplified my example in order to protect the developers /
database designers.

Here's the question, re-stated: "How, in an SQL statement, can I
determine if a CHAR(8) column contains a vaild date?".

- Lock Lyon
Principal DBA
Fifth Third Bancorp

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of wtrovijo
Sent: Friday, January 19, 2007 8:50 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Testing for Invalid Dates

Hard to say without knowing what you're trying to do. Best way is to
define it as a date column.
If there's some kind of application/environment compatibility issues
with date columns, I'd say that application should take the
responsibility of assuring valid AND correct dates are used when
inserting/updating. Also, it does not make much sense to me - again, I'm
trying to figure out the whole picture - to set current date instead of
a invalid date; let's say you're trying to insert some kind of
expiration date to be two years from now, date is wrong you then set
expiration date to today...

If table is populated already and you just want to fix data
periodically, I would suggest a program to select using date function
from the column and handling -181 to fix invalid dates and trying to
interpret and set date values as precisely as possible.

HTH,
Walter Trovijo Jr.


This e-mail transmission contains information that is confidential and
may be privileged. It is intended only for the addressee(s) named
above. If you receive this e-mail in error, please do not read, copy or
disseminate it in any manner. If you are not the intended recipient, any
disclosure, copying, distribution or use of the contents of this
information is prohibited. Please reply to the message immediately by
informing the sender that the message was misdirected. After replying,
please erase it from your computer system. Your assistance in correcting
this error is appreciated.

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Walter Trovijo Jr (UOL)

Re: Testing for Invalid Dates
(in response to Richard Fazio)
Jacquie, I've suggested a program already but Lyon didn't seem to like it.


Anyway, I'd code something like this:

SELECT DATE(COLUMN_NAME) INTO :COLUMN-NAME FROM OWNER.TBNAME WHERE COL=:KEY-VALUE

IF SQLCODE = -181
PERFORM
DISPLAY 'INVALID DATE ' COLUMN-NAME ', RECORD ' KEY-VALUE
END-PERFORM
END-IF

If char column is not in a format supported by DATE function it can be converted in a valid DATE function
argument using cobol move and then:

EXEC SQL
SET :COLWK-NAME = DATE(:COLUMN-NAME)
END EXEC

IF SQLCODE = -181
PERFORM
DISPLAY 'INVALID DATE ' COLUMN-NAME ', RECORD ' KEY-VALUE
END-PERFORM
END-IF

By the way, I'm not a cobol expert but I'm almost sure that COBOL or LE-RUNTIME will have a function to check for valid dates
that might be 'cheaper' than DB2 call.

Walter Trovijo Jr.



Another convoluted way:
<snip>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

[login to unmask email]

Re: Testing for Invalid Dates
(in response to Walter Trovijo Jr (UOL))
select
date (date_var) from sysibm.sysdummy1;

The table sysibm.sysdummy1 is a one row, one column table specifically
designed for such things (like date, time, timestamp verification);

Dean J. Burchill
248-576-8319
(8) 776-8319




Mike Hutchinson <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
01/19/2007 10:27 AM
Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>


To
[login to unmask email]
cc

Subject
Re: [DB2-L] Testing for Invalid Dates







But a UDF returning NULL when the date is invalid would have less
system-wide impact. At least it's simple to use once it's coded.

Mike Hutchinson
Sr Applications DBA
517-367-3488 (x23488)
Jackson National Life


"Lyon, Lockwood" <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
01/19/2007 09:53 AM

Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>


To
[login to unmask email]
cc

Subject
Re: [DB2-L] Testing for Invalid Dates








Well, with this solution I think you'd also need to consider months with
< 31 days, and February in leap year. While I certainly could code such
a statement, I was hoping for a simple function (or set of functions)
that would return NULL (or something) if the conversion of the string
failed.

Another Lister suggested handling the SQLCode -181. I don't think this
is possible within an SQL statement, and would love to see an example.

If we move 'outside' SQL for the answer, then there are already several
non-SQL ways of handling an invalid date. We already know of these;
it's the "within a single SQL statement" that we were wondering about.

- Lock Lyon
Principal DBA
First Third Bancorp

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of [login to unmask email]
Sent: Friday, January 19, 2007 9:43 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Testing for Invalid Dates

You could write a CASE statement to parse the string for values in a
range:

case
when substr(test_date_char, 1, 4) > '1900'
and substr(test_date_char, 1, 4) < '2100'
and substr(test_date_char, 5, 2) > '00'
and substr(test_date_char, 5, 2) < '13'
and substr(test_date_char, 7, 2) > '00'
and substr(test_date_char, 7, 2) < '32'
then test_date_char
else 'invalid date'
end as valid_date




wtrovijo

<[login to unmask email]

.BR>
To
Ext: [login to unmask email]

Sent by: DB2 Data
cc
Base Discussion

List
Subject
<[login to unmask email] Re: [DB2-L] Testing for Invalid

ORG> Dates





01/19/2007 09:21

AM





Please respond to

DB2 Database

Discussion list

at IDUG

<[login to unmask email]

2-L.ORG>









So the short answer, imho, is to handle -181.

> I greatly simplified my example in order to protect the developers /
> database designers.
>
> Here's the question, re-stated: "How, in an SQL statement, can I
> determine if a CHAR(8) column contains a vaild date?".
>
> - Lock Lyon
> Principal DBA
> Fifth Third Bancorp
>



This e-mail transmission contains information that is confidential and may
be privileged. It is intended only for the addressee(s) named above. If
you receive this e-mail in error, please do not read, copy or disseminate
it in any manner. If you are not the intended recipient, any disclosure,
copying, distribution or use of the contents of this information is
prohibited. Please reply to the message immediately by informing the
sender that the message was misdirected. After replying, please erase it
from your computer system. Your assistance in correcting this error is
appreciated.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

M. Khalid Khan

Re: Testing for Invalid Dates
(in response to DB46@DAIMLERCHRYSLER.COM)
Since a SP call can't be included in a SQL, a better idea would be have a
UDF that takes a character string and returns date( or null when invalid ).
Such an UDF can be used in SQL anyway you like.





"Kenney, Marty"
<[login to unmask email]
LINC.COM> To
Sent by: "DB2 [login to unmask email]
Data Base cc
Discussion List"
<[login to unmask email] Subject
ORG> Re: [DB2-L] Testing for Invalid
Dates

01/19/2007 07:43
PM


Please respond to
"DB2 Database
Discussion list
at IDUG"
<[login to unmask email]
2-L.ORG>






If a program will work for you then the very simple SQL stored procedure
works fine:

CREATE PROCEDURE AMPSPASD.NAK1
(INOUT DATE_INPUT CHAR(10))
FENCED
EXTERNAL NAME 'NAK1'
DYNAMIC RESULT SETS 0
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
NO DBINFO
COLLID AMPSPASD
WLM ENVIRONMENT DB2BSTLD
ASUTIME NO LIMIT
STAY RESIDENT NO
SECURITY DB2
RUN OPTIONS 'MSGFILE(SYSOUT,FBA,121,0,ENQ)'
COMMIT ON RETURN NO
P1: BEGIN NOT ATOMIC
DECLARE CONTINUE HANDLER FOR SQLSTATE '22007'
SELECT CHAR(CURRENT DATE)
INTO DATE_INPUT FROM SYSIBM.SYSDUMMY1;
SELECT CHAR(DATE(DATE_INPUT))
INTO DATE_INPUT FROM SYSIBM.SYSDUMMY1;
END P1


Marty Kenney
Sr. DB2 DBA
(919) 651-5211



---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm
---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm





**********

The information contained in this communication is confidential, private, proprietary, or otherwise privileged and is intended only for the use of the addressee. Unauthorized use, disclosure, distribution or copying is strictly prohibited and may be unlawful. If you have received this communication in error, please notify the sender immediately at (312)653-6000 in Illinois; (800)835-8699 in New Mexico; (918)560-3500 in Oklahoma; or (972)766-6900 in Texas.

**********

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm