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).
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 CRPence
Gesendet: Friday, 08.7 2016 00:15
An: midrange-l@xxxxxxxxxxxx
Betreff: Re: SQL convert text mm/dd/yy to date and timestamp
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/d
b2/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.
--
Regards, Chuck
--
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.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
As an Amazon Associate we earn from qualifying purchases.