Cast varchar type to date

Question

I'd like to change a specific column in my PostgreSQL database from character_varying type to type date. Date is in the format yyyy:mm:dd

I tried to do:

alter table table_name
alter column date_time type date using (date_time::text::date);

But I received an error message:

date/time field value out of range: "2011:06:15"

Solution

When you cast text or varchar to date, the default date format of your installation is expected - depending on the datestyle setting in your postgresql.conf.

Generally, colon (:) is a time separator, In a simple cast, PostgreSQL will probably try to interpret '2011:06:15' as time - and fail.

To remove ambiguity use to_date() with a matching pattern for your dates:

ALTER TABLE table_name
ALTER COLUMN date_time type date
USING to_date(date_time, 'YYYY:MM:DD'); -- pattern for your example

Comments

[Lo Bizzarri]

Thanks a lot, it works! But now I have the same problem with the hour column. It's a string and I'd like to trasform it in time type, but I don't know how to do it. Can you help me even with this problem? Thanks in advance.

[Erwin Brandstetter]

@LoBizzarri: Piece of cake with to_timestamp(). Did you follow the link I provided? If it's still giving you static, post another question with example of what you have and what you want.

[Lo Bizzarri]

Sorry, yesterday I had made ​​a mistake in the command line. Now it is ok, thanks! I used: ALTER TABLE table_name ALTER COLUMN hour_time type time USING to_timestamp(hour_time, 'HH24:MI:SS');