Error in Casting date postgreSQL

Question

I'm trying to cast a varchar into a date with this following code, and the following error is outputting, any idea why?

ALTER TABLE import.employee
ALTER COLUMN birth_date
TYPE date
USING (birth_date::date);

    ERROR:  date/time field value out of range: "05/29/1960"
HINT:  Perhaps you need a different "datestyle" setting.
********** Error **********

ERROR: date/time field value out of range: "05/29/1960"
SQL state: 22008
Hint: Perhaps you need a different "datestyle" setting.

Clodoaldo Neto · Accepted Answer · 2014-04-27 21:08:03Z

7

Set the datestyle before:

set datestyle = mdy;

answered Apr 27, 2014 at 21:08

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

2 Comments

mu is too short Over a year ago

Or using (to_date(birth_date, 'MM/DD/YYYY')) if you don't want to mess around with or worry about connection settings.

DodoSombrero Over a year ago

Worked like a charm Clodoaldo :)

Sergio B. · Accepted Answer · 2014-04-27 21:25:56Z

3

If you need to get the ::date from birth_date, first give the DATE format to your field using the given functions in Data Type Formatting Functions docs.

answered Apr 27, 2014 at 21:25

Sergio B.

813 bronze badges

Collectives™ on Stack Overflow

Error in Casting date postgreSQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related