Re: help building datetime from varchars

You 30 minutes are getting converted to 30 months.
Use DD/MM/YYYYHH24:MI:SS instead.



Brent Wood wrote:

> 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
limit1; 
>       ?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
>