Postgresql date format error

Question

I'm having problems when changing the date format for the datepicker. I want to have the 'dd/mm/yyyy/ date format but postgres throws me this error:

ActiveRecord::StatementInvalid - PG::DatetimeFieldOverflow: ERROR:  date/time field value out of range: "16/07/2014"
Perhaps you need a different "datestyle" setting.

because of its american way of saving the date format.

Ilesh Patel · Accepted Answer · 2014-07-22 11:50:54Z

4

Set datestyle in your postgreSQL database as below:

SET datestyle = "ISO, DMY";

answered Jul 22, 2014 at 11:50

Ilesh Patel

2,15516 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bogdan Popa Over a year ago

I edited the postgresql.conf file line "datestyle = 'iso, mdy' to 'iso, dmy' but I get the same result..

Ilesh Patel Over a year ago

Have you restart postgreSQL after changing the file?

Community · Accepted Answer · 2017-05-23 12:10:19Z

4

The datestyle setting defines a default for your system. Consider @Ilesh's answer. The setting in postgresql.conf applies to all databases in the db cluster.

To make statements work without regard to the setting, use the to_date() function to form date values from string constants:

SELECT to_date('16/07/2014', 'DD/MM/YYYY');

Related:
Store date with optional month / day

edited May 23, 2017 at 12:10

CommunityBot

11 silver badge

answered Jul 22, 2014 at 12:21

Erwin Brandstetter

668k160 gold badges1.2k silver badges1.3k bronze badges

Collectives™ on Stack Overflow

Postgresql date format error

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related