The Funktion ist described as
to_date(text, text) date convert string to date to_date('05 Dec =
2000', 'YYYYMMDD')
The 2 Pattern DD/HH are not well working
All Digs ar counted from Position6/8 are counted as Days or Hour
and produce a future Date or error from a Timestamp in Milliseconds
=20
The description
DD day of month (01-31)
HH hour of day (01-12)
Fore Example:
- Full Time in include ms 2015-12-02 11:59:09.001=20
-- Fulltimestamp 20151202115909001
SELECT to_date('20151202115909001','YYYYMMDD') AS =
der_2_december_eleven_a_clock
-> "5795177-12-29=E2=80=9C =3D> fare away in the future
SELECT to_date('20151202115909001','YYYYMMDDHH') AS =
der_2_december_eleven_a_clock
ERROR: hour "115909001" is invalid for the 12-hour clock
HINT: Use the 24-hour clock, or give an hour between 1 and 12.
********** Fehler **********
ERROR: hour "115909001" is invalid for the 12-hour clock
SQL Status:22007
Hinweis:Use the 24-hour clock, or give an hour between 1 and 12.
- Full Time in include ms 2015-12-01 11:59:09.001=20
-- Fulltimestamp 20151222115909001
SELECT to_date('20151222115909001','YYYYMMDD') AS =
22_december_eleven_a_clock
ERROR: value for "DD" in source string is out of range
DETAIL: Value must be in the range -2147483648 to 2147483647.
********** Fehler **********
ERROR: value for "DD" in source string is out of range
SQL Status:22008
Detail:Value must be in the range -2147483648 to 2147483647.
The other Pattern works as Described
SELECT to_date('20151202115909001','YYYYMMDDHH24') AS =
der_2_december_eleven_a_clock
-> "2015-12-02"
My first workaround in the script was to take only the first 8 Digs=20
SELECT to_date(left(=E2=80=9A20151222115909001=E2=80=98,8),'YYYYMMDD') =
AS 22_december_eleven_a_clock
"2015-12-22"
other description of the timestamp works also fine=20
SELECT to_date(left('20151202115909001',8),'YYYYMMDDHH24MISSMS') AS =
der_first_december_eleven_a_clock
SELECT to_date('20151202115909001','YYYYMMDDHH24MI') AS =
der_2_december_eleven_a_clock
Mit freundlichen Gr=C3=BC=C3=9Fen
Andreas.Rhode@gmx.de