CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
SQL Enthusiast
SQL Enthusiast

Reputation: 69

Varchar to Date or Timestamp in snowflake

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: enter image description here

Upvotes: 0

Views: 1102

Answers (2)

Anand
Anand

Reputation: 564

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;

Upvotes: 2

demircioglu
demircioglu

Reputation: 3455

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

Upvotes: 1

Related Questions