Re: SQL convert text mm/dd/yy to date and timestamp

I have some dates stored as text mm/dd/yy and others stored as m/dd/yy.

I thought that SQL was smart enough to convert all of the following month
descriptors to a timestamp:

'01'
'1'
'12'


But I run the following SQL and see the errors below where the dates (char
format) fail to convert to timestamps because the month is 2 digits and SQL
cant seem to handle.

Job defn: Date format . . . . . . . . . . . . . . . . . . . : *MDY

(Does the date format in the job defn affect SQL?)

Any ideas?

I would like to have both 01/02/03 and also 1/2/03 both convert to Jan 2
2003 but SQL doesn't seem to support this as shown below.

Thanks!


select substr(docid,1,30) , substr(keyword3,1,10),
chkdate,
timestamp(date(substr(keyword3,1,10)),time('00:00 AM'))

from rjsimage/docs00
where doctype2 = 'COUNTRYOPSFMS'



SUBSTR SUBSTR TIMESTAMP
AS400DOC-000000016711268 1/01/01 ++++++++++++++++++++++++++
< 1 digit month before slash fails to convert to timestamp -
Why?
AS400DOC-000000016711331 1/01/01 ++++++++++++++++++++++++++
AS400DOC-000000016711344 1/01/01 ++++++++++++++++++++++++++
AS400DOC-000000016711349 1/01/01 ++++++++++++++++++++++++++
AS400DOC-000000016711369 1/01/01 ++++++++++++++++++++++++++
AS400DOC-000000016711526 12/18/13 2013-12-18-00.00.00.000000
< 2 digit month successfully converts to timestamp
AS400DOC-000000016711512 1/02/14 ++++++++++++++++++++++++++
AS400DOC-000000016711517 1/06/14 ++++++++++++++++++++++++++
AS400DOC-000000016711505 1/07/14 ++++++++++++++++++++++++++

______________________________________________________________________
This outbound email has been scanned for all viruses by the MessageLabs
Skyscan service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________
--
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.