On 28-Oct-2014 18:25 -0500, CRPence wrote:
On 28-Oct-2014 16:26 -0500, Stone, Joel wrote:
<<SNIP>>
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 ≥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.
..."
Revisiting that page just now, I see the above as the immediately
following text:
"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'.
..."
<<SNIP>>
I expect the following query will produce the report that follows,
such that there would be no data mapping errors:
<ed: added CTE so query self-contained; table-ref uses CTE>
with dtCTE (keyword3) as ( values('1/02/14'),('12/18/13') )
select
substr(keyword3, 1, 10)
, to_date(substr(keyword3,1,10), 'MM/DD/YY') "TimeStamp"
from dtCTE /* rjsimage/docs00 */
/* <ed: remove selection */
-- 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
Apologies for necroposting, but I ran across the above quoted message
while researching something else, and I found that my assumptions made
back then seem incorrect; at least now that I am able to test on IBM i
7.1. The above query gives a mapping error for the value lacking a
leading zero for the 'MM' component.
I wonder if anyone can get a valid TIMESTAMP [rather than the data
mapping errors for the following query TO_DATE expression, which is
mostly just an expansion of data for the above query] 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 [both
what is from my quoted\old message and an addendum from this reply], 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 ********
Revising the above CTE and TO_DATE usage to match their expression
and [even a bit more aggressive on the YYYY component than with their]
example data, for what is their allusion of support for date\timestamp
components lacking leading zeroes, I get exactly the result the docs
suggest; 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 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.
midrange.com
