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

On 08-Jul-2016 00:38 -0500, Birgitta Hauser wrote:

On Friday, 08.7 2016 00:15 CRPence wrote:

<<SNIP and combine doc link with contiguous quoted snippet:>>

[http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_71/db2/rbafzscatsformat.htm]
"A substring of the string-expression representing a component of a
≥date or≤ timestamp (such as year, month, day, hour, minutes,
seconds) can include less than the maximum number of digits for
that component of the ≥date or≤ timestamp. Any missing digits
default to zero. For example, with a format-string of 'YYYY-MM-DD
HH24:MI:SS', an input value of '999-3-9 5:7:2' would produce the
same result as '0999-03-09 05:07:02'.

Leading zeroes can be specified for any component of the ≥date or≤
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.
..."

<<SNIP>>

I wonder if anyone can get a valid TIMESTAMP [rather than the data
mapping errors for the following query TO_DATE expression <<SNIP>>]
when running the following query on any of [a somewhat up-to-date]
v7r1, v7r2, or v7r3?:


with dt2v (c8) as (
values('01/02/03'), ( '8/6/14'), ( '5/10/15')
,('09/7/16' ), ( '3/4/7' ), ('12/18/30')
,('12/30/42') )
select substr(c8 , 1, 08) as c8
, to_date(substr(c8 , 1, 08), 'MM/DD/YY') as c8_to_date
, date( insert( c8
, locate('/', c8, 4) + 1
, case when length(rtrim(c8))>5 then 0 end
, case when right( rtrim(c8), 2 )
between '00' and '39' then '20'
else '19'
end
)
) as usa_date_fmt
from dt2v
; -- STRSQL report I get, running with ISO date formatting:
....+....1....+....2....+....3....+....4....+....5
C8 C8_TO_DATE USA_DATE_FMT
01/02/03 2003-01-02-00.00.00.000000 2003-01-02
8/6/14 ++++++++++++++++++++++++++ 2014-08-06
5/10/15 ++++++++++++++++++++++++++ 2015-05-10
09/7/16 ++++++++++++++++++++++++++ 2016-09-07
3/4/7 ++++++++++++++++++++++++++ -
12/18/30 2030-12-18-00.00.00.000000 2030-12-18
12/30/42 2042-12-30-00.00.00.000000 1942-12-30
******** End of data ********


Of course given the prior v7r1 doc reference snippet I included
<<SNIP>>, I am expecting instead, from what I infer, from what
that doc suggests, is the following report, without any mapping
errors:

....+....1....+....2....+....3....+....4....+....5
C8 C8_TO_DATE USA_DATE_FMT
01/02/03 2003-01-02-00.00.00.000000 2003-01-02
8/6/14 2014-08-06-00.00.00.000000 2014-08-06
5/10/15 2015-05-10-00.00.00.000000 2015-05-10
09/7/16 2016-09-07-00.00.00.000000 2016-09-07
3/4/7 2007-03-04-00.00.00.000000 -
12/18/30 2030-12-18-00.00.00.000000 2030-12-18
12/30/42 2042-12-30-00.00.00.000000 1942-12-30
******** End of data ********


<ed: reworded; to emphasize example is from the doc ref above>
Revising the above CTE and TO_DATE usage to match the expression
and example data from the docs, for what is their allusion of
support for date\timestamp components lacking leading zeroes, I get
exactly the result the docs suggest [even whilst being a bit more
aggressive on the YYYY component than with their example data];
happily, without data mapping errors!:


with dt2v (cv) as (
values('9-3-9 5:7:2' )
,('99-3-9 5:7:2' )
,('999-3-9 5:7:2' )
,('0999-03-09 05:07:02' ) )
select substr(cv , 1, 19) as cv
, to_date(substr(cv , 1, 19), 'YYYY-MM-DD HH24:MI:SS')
as cv_to_ts
from dt2v
; -- the report I get follows:

....+....1....+....2....+....3....+....4....+..
CV CV_TO_TS
9-3-9 5:7:2 0009-03-09-05.07.02.000000
99-3-9 5:7:2 0099-03-09-05.07.02.000000
999-3-9 5:7:2 0999-03-09-05.07.02.000000
0999-03-09 05:07:02 0999-03-09-05.07.02.000000
******** End of data ********

So <ed: that the above example is functional> leaves me wondering
"Why the failures using my earlier examples?"

> p.s. Despite the claim by those docs that "Separator characters

can also be specified at the start or end of format-string. These
separator characters can be used in any combination in the format
string, for example 'YYYY/MM-DD HH:MM.SS'. Separator characters
specified in a string-expression are used to separate components
and are not required to match the separator characters specified in
the format-string." simple changes to use '/' vs '-' often causes
the pseudo-builtin to fail as the [system-supplied] UDF that it is,
with msg CPF426B rc13 F/QQQSVRTN FM/QQINVUDF FP/SIGNALUDFERROR with
an implication that I should "Ensure that the format string
correctly describes the timestamp string specified." -- ironic, as
that seems almost to suggest that I need to match the separators,
when clearly the doc implies *not*! The UDF seems IMO, to be way
too fickle to be trusted generally; I would probably tend to avoid
using the feature in production, esp. if the input data might not
be very exacting and well tested with that UDF.

If you are working with a character representation of a date with a
date format with a 2 digit year, leading zeros are required for the
day and month specifications (this is valid for all date conversion
methods in SQL and even RPG).

I concede that "Leading zeros can be omitted from the month and day portions [only] when using the IBM® SQL standard formats", of which there are only 4-digit-year SQL standard formats, holds true for the standard\typical casting scalars from a Date String. But that is not germane. Re Date Strings, see:
IBM i->IBM i 7.1->Database->Reference->SQL reference->Language elements->Data types->Datetime values->String representations of datetime values->Date strings
[http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_71/db2/rbafzdatestrings.htm]

But the documentation for TIMESTAMP_FORMAT, for which "The TIMESTAMP_FORMAT function returns a timestamp that is based on the interpretation of the input string using the specified format." does not require any of those formal\standard Date String variations, because that pseudo-builtin scalar function allows for the specification of a /format-string/ to be specified, with various /components/ that are [best I can infer] *conspicuously documented* to support the lack of a leading zero when /interpreted/. In fact there are examples where clearly this function handles such data, even for the year component; e.g. little different than my prior failing example:

with dt2v (cv) as (
values('1.2.3'), ('5.6.11')
,('99.3.9 ' ) )
select substr(cv , 1, 07) as cv
, to_date(substr(cv , 1, 10), 'YY.MM.DD' )
as cv_to_ts
from dt2v
; -- report from the above query:
....+....1....+....2....+....3....+
CV CV_TO_TS
1.2.3 2001-02-03-00.00.00.000000
5.6.11 2005-06-11-00.00.00.000000
99.3.9 2099-03-09-00.00.00.000000
******** End of data ********

And simply by changing the /separator/ character in any one value, which is documented [again quite conspicuously] to be effectively irrelevant to the capability\functionality of the pseudo-builtin scalar UDF, suddenly sees the function fail to convert a value:

with dt2v (cv) as (
values('1.2.3'), ('5.6.11')
,('99-3-9 ' ) )
select substr(cv , 1, 07) as cv
, to_date(substr(cv , 1, 10), 'YY.MM.DD' )
as cv_to_ts
from dt2v
; -- report from the above query:
*none, instead: msg SQ20448 "Expression not valid using format string specified for TIMESTAMP_FORMAT." per msg CPF426B rc13 followed by msg CPF503E ec24 rc24 per error invoking the associated external program or service program QQQSVUSR with program entry point or external name QQQTimestamp_Format


One question is, why does this feature so unpredictably both support and fail to support, various values as input, while making such bold claims of support\capability? And if unsupported, then please just fail consistently, instead of giving the impression that the apparently deceptively worded docs are honest about what should be supported -- and drop all the then-presumably false-claims from the docs. I am still wondering if anyone on up-to-date v7r1 or more recent releases can suggest that by their tests, the feature can perform without these errors, thus apparently eventually providing consistent support for date\time components lacking the leading zero.