CODEWITHSUNDEEP
sqldatetimestampamazon-redshift
simplycoding
simplycoding

Reputation: 2977

Converting a varchar field into a date field

Need help converting a varchar field into a timestamp.

The field is in the format of '5/2/2017 9:45:29 AM' so MM/DD/YYYY HH:MI:SS AM

I've been able to get the year, month, and date but can't get the hour, minute and seconds. This is what I've done:

SELECT
    '5/2/2017 9:45:29 AM' as data
    , to_date('5/2/2017 9:45:29 AM', 'MM/DD/YYYY HH24:MI:SS')
    , to_char(to_date('5/2/2017 9:45:29 AM', 'MM/DD/YYYY HH12:MI:SS PM'), 'YYYYMMDD')

That returns: 

data                | to_date             | to_char
------------------------------------------------------------
5/2/2017 9:45:29 AM | 2017-05-02 00:00:00 | 20170502

I've been playing around with the to_date function but can't get anything to work. I had AM included as well, but that did nothing. Not sure what else to try at this point

Upvotes: 0

Views: 48

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51649

to timestamp:

t=# select cast('5/2/2017 9:45:29 AM' as timestamp);
      timestamp
---------------------
 2017-05-02 09:45:29
(1 row)

and you can cast it to timestamp and then cast to time:

t=# with t as (
  with v as (
    select '5/2/2017 9:45:29 AM'::text v
  )
  select v::timestamp t
  from v
)
select t::date to_date, t::time to_time, t to_timestamp
from t;
  to_date   | to_time  |    to_timestamp
------------+----------+---------------------
 2017-05-02 | 09:45:29 | 2017-05-02 09:45:29
(1 row)

Upvotes: 2

Related Questions