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

From: Joel.Stone@xxxxxxxxxx
To: midrange-l@xxxxxxxxxxxx
Subject: RE: SQL convert text mm/dd/yy to date and timestamp
Date: Wed, 29 Oct 2014 21:34:03 +0000

Thanks Birgitta & Chuck for all the assistance.

I should have mentioned this particular iseries in on v5r4, which doesn't support the Timestamp_Format I don't think.

But great idea!

Any ideas what to do on v5r4?

Is the simplest method to use the CASE statement to build two conditions? (One for single digit month and another for 2 digit month)?

Thanks

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Birgitta Hauser
Sent: Tuesday, October 28, 2014 9:16 PM
To: 'Midrange Systems Technical Discussion'
Subject: AW: SQL convert text mm/dd/yy to date and timestamp

Only date formats with a 4 digit year and the "right" separator can be
translated into a date or timestamp, but you may convert your character date
into a timestamp by using the TIMESTAMP_FORMAT scalar function.

Timestamp_Format(YourCharDate, 'MM/DD/YY')

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"


-----Ursprüngliche Nachricht-----
Von: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von
Stone, Joel
Gesendet: Tuesday, 28.10 2014 22:27
An: midrange-l@xxxxxxxxxxxx
Betreff: 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.


--
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 inbound email has been scanned for all viruses by the MessageLabs SkyScan
service.
________________________________________________________________________

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