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

[Alan Campin <alan0307d@xxxxxxxxx>]

I found the same thing and in iDate I added code to normalize the date
before it attempts to convert it. See the code at
www.think400.dk/downloads.htm under downloads or just use iDate.

On Tue, Oct 28, 2014 at 3:26 PM, Stone, Joel <Joel.Stone@xxxxxxxxxx> wrote:

> 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.