Обсуждение: to_date() not works as described for pattern DD & HH

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

Andreas Rhode <Andreas.Rhode@gmx.de> writes:
> 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

As far as I can see, the problem here is that you're not giving a pattern
that accurately describes the input data.

> SELECT to_date('20151202115909001','YYYYMMDD') AS der_2_december_eleven_a_clock
> -> "5795177-12-29“     => fare away in the future

A correct format for this input would be something like YYYYMMDDHH24MISSMS:

# SELECT to_timestamp('20151202115909001','YYYYMMDDHH24MISSMS');
        to_timestamp
----------------------------
 2015-12-02 11:59:09.001-05
(1 row)

I think what is happening in your example is that it's including all the
remaining adjacent digits in the DD field.  While this seems silly here,
it's less silly in, for example,

# SELECT to_date('201512021','YYYYMMDD');
  to_date
------------
 2015-12-21
(1 row)

But it's not really to_date's job to parse input that is not correctly
described by the given format.

            regards, tom lane