How to replace Hour/Minutes/Seconds in Snowflake?-CodePudding

I have a column with datetime values that look like this:

I would like to replace all the hours/minutes and seconds to be all 0s. something like this:

Is there a way to do this?

CodePudding user response：

Try use DATE_TRUNC function:

SELECT date_trunc('DAY', '2021-01-27 14:00:28'::datetime);

CodePudding user response：

Using casting:

SELECT col_name::DATE
FROM table;

CodePudding user response：

Try this: SELECT cast(CAST('2021-01-27 14:00:28' AS date) as datetime);

Be aware that the DATE_TRUNC function is not available in some versions of SQL.

CodePudding user response：

You can use REGEXP_REPLACE:

select regexp_replace('2021-12-02 01:03:50', ' [0-9]{2}:[0-9]{2}:[0-9]{2}',' 00:00:00') as date;

I get:

2021-12-02 00:00:00

Using it on a table:

create or replace table t_time(t varchar(100));
insert into t_time values ('2021-12-02 01:35:20');
select t from t_time;
select regexp_replace(t, ' [0-9]{2}:[0-9]{2}:[0-9]{2}',' 00:00:00'), t from t_time;