Thread: Should I use CAST?

I have two tables that are basically the same.  They have different data types through.  I am trying to load data from one table to another and I get the error:

ERROR:  Attribute 'cert_expiration' is of type 'date' but expression is of type 'varchar'
        You will need to rewrite or cast the expression

So I tried this:

-----------------------------------------------------
INSERT INTO ebs_webserver_t (
install_directory,
server_instance_nme,
docs_directory_nme,
other_info,
port_num,
host_nme,
ip_num,
cert_expiration,
dns_nme,
url_nme,
app_contact_id,
cert_type_id,
domain_id,
node_id, 
environment_id,
sbu_id,
server_type_id
)
SELECT
install_directory,
server_instance_nme,
docs_directory_nme,
other_info,
port_num,
host_nme,
ip_num,
CAST(cert_expiration AS DATE),
dns_nme,
url_nme,
app_contact_nme,
cert_type_id,
domain_id,
node_id,
environment_id,
sbu_id,
server_type_id
FROM ebs_webserver_tmp2;
---------------------------------------------------------

Notice I used CAST in the SELECT.  But I get this error:

ERROR:  Cannot cast type 'varchar' to 'date'

Any ideas what I can do to make a VARCHAR column a date?
All the dates are in this format: 10/23/2002

Thanks!

[:==> Troy Campano <==:]
Database (Request Manager/web/database)
http://intranet:3000/infosys/infra/software/database/dbapp/
http://intranet2/reqman/
Microsoft gave you Windows, UNIX the whole house.

"Campano, Troy" <Troy.Campano@LibertyMutual.com> writes:
> Any ideas what I can do to make a VARCHAR column a date?
> All the dates are in this format: 10/23/2002

Use to_date().  See

http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/functions-formatting.html

            regards, tom lane