Varchar to Date or Timestamp in snowflake-CodePudding

I have a column date_sent which is in varchar format. I want to capture only date.

select
date_sent,
TRY_TO_TIMESTAMP(date_sent,'MM/DD/YYYY HH:MI:SS AM') send_ts,

send_ts::date as send_date

tried TO_DATE(date_sent) send_ts as well but got error:

Date '1/6/2022 6:01:00 PM' is not recognized

output:

CodePudding user response：

You can also use to_date func and covert:

with tbl as (select '1/6/2022 6:01:00 PM'::varchar dt)
select TO_date(dt, 'mm/dd/yyyy hh:mi:ss PM')::date dt
from tbl;

CodePudding user response：

Since timestamp in varchar format you can capture the date part with the following

select substr(date_sent,1,position(' ',ts))::date

Example :

with data as (select '1/6/2022 6:01:00 PM' date_sent)
select substr(date_sent,1,position(' ',date_sent))::date dt
from data

returns

2022-01-06