Q:

Cast varchar type to date

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"

A:

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

