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

On 28-Oct-2014 16:26 -0500, Stone, Joel 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'

The SQL is indeed /smart enough/ to effect those conversions, wherever that capability is documented as being supported. For example, using Timestamp Strings:
<http://www.ibm.com/support/knowledgecenter/api/content/nl/en/ssw_ibm_i_71/db2/rbafztsstrings.htm>
"...
Trailing blanks can be included. Leading zeros can be omitted from the month, day, hour, ≥minute,≤ and second part of the timestamp when using the timestamp form with separators. ...
..."

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 can't seem to handle.

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

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

The Date Format (DATFMT) attribute of the job plays a role *only* when the SQL Date Format (DATFMT) attribute has resolved from the special value *JOB.

Any ideas?

There is an associated Date Separator (DATSEP) for both the job and the SQL, relevant for any non-standards [all of the support "job"] date formats; e.g. *MDY, *DMY, *YMD, *JUL

Also never /assume/ the DatFmt for the job for use in the SQL. Best to either always explicitly set the SQL environment to use a known format, or always use only the date formats that are the recognized /standards/ of *ISO, *EUR, *JIS, *USA; thus either way, the date format and separator of the job are moot, because the assumption has been explicitly established and thus can not become a failed assumption.

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

The TIMESTAMP_FORMAT() casting scalar [TO_DATE() as a synonym] I expect will support that format, using the 'YY' in the formatting string to denote the default 100-year window; I can not test to verify the results, and the docs are not succinct and clear in that regard. That scalar documents the same support as Timestamp Strings, though with an inverse wording:

<http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_71/db2/rbafzscatsformat.htm>
"... Leading zeroes can be specified for any component of the timestamp value (for example, month, day, hour, minutes, seconds) that does not have the maximum number of significant digits for the corresponding format element in the format string.
..."

as shown below.

select
substr(docid,1,30)
, substr(keyword3,1,10)
, chkdate

The output from the above column was omitted from the following report, so I reformatted that report to omit the result of the first expression as well as limit the data to two specific rows that exhibit the issue [and updated the below WHERE clause accordingly]:

, timestamp( date(substr(keyword3,1,10))
, time('00:00 AM') )
from rjsimage/docs00
where doctype2 = 'COUNTRYOPSFMS'

and substr(keyword3, 1, 10) in ('1/02/14', '12/18/13')

SUBSTR TIMESTAMP
1/02/14 ++++++++++++++++++++++++++ <- 1 digit month before slash fails to convert to timestamp. Why?
12/18/13 2013-12-18-00.00.00.000000 <- 2 digit month successfully converts to timestamp

The problem is presumably that there is no support for omitted leading zeroes in Date Strings; i.e. unlike the above doc link for Timestamp Strings explicitly stating such support exists, there is no similar statement for date, thus the DATE casting fails.


Thus I expect that the mapping error is actually for the DATE() being performed within the TIMESTAMP casting scalar, thus the following query likely has the report that follows, exposing that the DATE() itself presumably would have failed:

select
substr(keyword3,1,10)
, date(substr(keyword3,1,10) "Date"
from rjsimage/docs00
where doctype2 = 'COUNTRYOPSFMS'
and substr(keyword3, 1, 10) in ('1/02/14', '12/18/13')
-- the following report:

SUBSTR "Date"
1/02/14 ++++++++++ <- 1 digit month; convert to date fails
12/18/13 2013-12-18 <- 2 digit month; converts to a date

I expect the following query will produce the report that follows, such that there would be no data mapping errors:

select
substr(keyword3,1,10)
, to_date(substr(keyword3,1,10), 'MM/DD/YY') "TimeStamp"
from rjsimage/docs00
where doctype2 = 'COUNTRYOPSFMS'
and substr(keyword3, 1, 10) in ('1/02/14', '12/18/13')
-- the following report:

SUBSTR "TimeStamp"
1/02/14 2014-01-02-00.00.00.000000
12/18/13 2013-12-18-00.00.00.000000