0
select to_date('20170202','YYYYMMDD');

returns: 

2017-02-02

but when i do:

select to_date('20172202','YYYYMMDD');

the output is : 

2018-10-04

when the Month is wrong why don't it returns an error as in Oracle?

Kamfasage
  • 181
  • 1
  • 7
  • 14

4 Answers4

2

I think here is a reason why this not causing an error:

to_timestamp and to_date exist to handle input formats that cannot be converted by simple casting. These functions interpret input liberally, with minimal error checking. While they produce valid output, the conversion can yield unexpected results. For example, input to these functions is not restricted by normal ranges, thus to_date('20096040','YYYYMMDD') returns 2014-01-17 rather than causing an error. Casting does not have this behavior.

https://www.postgresql.org/docs/9.6/static/functions-formatting.html

Oto Shavadze
  • 40,603
  • 55
  • 152
  • 236
  • ok.. thanks for your answer .. but i really one it to return an error when the date is wrong month>12 for example and day>32 .. how can i do this or what can i use – Kamfasage Apr 19 '17 at 15:37
  • @Kamfasage - If you try to cast `select cast('20172202' as date)`, you will get an error – Oto Shavadze Apr 19 '17 at 15:42
  • so i should do : ' select to_date ( cast('20172202' as date), YYYYMMDD); ' this wouldn't work because the **to_date** function has as parameters text and text – Kamfasage Apr 20 '17 at 06:51
2

Now this command raise error (this issue was fixed in PostgreSQL 10):

postgres=# select to_date('20172202','YYYYMMDD');
ERROR:  date/time field value out of range: "20172202"
Pavel Stehule
  • 42,331
  • 5
  • 91
  • 94
0

i have an answer of what i wanted/ i used the to_char instead of to_date an i cast the fisrt parameter into date..

select to_char(DATE '20162202,'YYYYMMDD') into datResult ;

so i can now have an error..

thanks for your help you all

Kamfasage
  • 181
  • 1
  • 7
  • 14
0

YYYYMMDD has to be YYYYDDMM months can not be greater then 12.

Stephan Hogenboom
  • 1,543
  • 2
  • 17
  • 29
user12355065
  • 9
  • 1