CODEWITHSUNDEEP
sqlpostgresqlto-date
Kamfasage
Kamfasage

Reputation: 201

how do to_date function in postgresql works?

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?

Upvotes: 0

Views: 6614

Answers (4)

user12355065
user12355065

Reputation: 9

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

Upvotes: 0

Pavel Stehule
Pavel Stehule

Reputation: 45910

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"

Upvotes: 3

Kamfasage
Kamfasage

Reputation: 201

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

Upvotes: 0

Oto Shavadze
Oto Shavadze

Reputation: 42803

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

Upvotes: 2

Related Questions