help building datetime from varchars

[Brent Wood <b.wood@niwa.co.nz>]

Hopefully someone can point me in the proper direction....

I have a table containg (amongst others) two varchar attrs date_s &
time_s.

They contain strings like:

   date_s   |  time_s
------------+----------
 01/10/1989 | 00:30:00

Can someone suggest an sql to turn these into a single datetime?




I have tried the following with results I don't understand:

env2003=# select date_s || time_s, to_timestamp(date_s || time_s,'DD/MM/YYYYHH:MM:SS') from event limit 1;
      ?column?      |      to_timestamp
--------------------+------------------------
 01/10/198900:30:00 | 1991-06-05 00:00:00+12
(1 row)

env2003=# select date_s || ' ' || time_s, to_timestamp(date_s || ' ' || time_s,'DD/MM/YYYY HH:MM:SS') from event limit 1;
      ?column?       |      to_timestamp
---------------------+------------------------
 01/10/1989 00:30:00 | 1991-06-05 00:00:00+12
(1 row)



Thanks,

   Brent Wood


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