converting varchar date strings to date

[pw]

Hello,

How can I typecast a date generated from VARCHAR fields into
a date field

ie:

UPDATE inventory SET date_field = vc_year||'-'||vc_month||'-'||vc_day;

where the date string is built up from varchar fields?

Thanks for any help.

Peter
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Richard Huxton]

On Tuesday 14 October 2003 17:54, pw wrote:

Hello,

How can I typecast a date generated from VARCHAR fields into
a date field

ie:

UPDATE inventory SET date_field = vc_year||'-'||vc_month||'-'||vc_day;

.... SET date_field = CAST(vc_year... vc_day AS date)
or
.... SET date_field = (vc_year...vc_d ay)::date

The first is SQL-standard, the second less typing.

If that gives you problems, cast to text first, then to date.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Peter Eisentraut]

pw writes:

How can I typecast a date generated from VARCHAR fields into
a date field

Using CAST().

--
Peter Eisentraut pe*****@gmx.net
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[pw]

Hello,

This has been resolved.
As I told a previous poster, CAST() wasn't working.
I have no idea why.

I finally used:

UPDATE inventory SET date_field=date (vc_year||'-'||vc_month||'-'||vc_day );

Peter

pw writes:

How can I typecast a date generated from VARCHAR fields into
a date field

Using CAST().

--
Peter Eisentraut pe*****@gmx.net

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster