How to change my timestamp into a date formatted as yyyy-01-01 in snowflake (sql)-CodePudding

I need to change a column of timestamps (date_col) to be formatted as yyyy-01-01. January first of the year in the date_col

Example:

actual                         desired

2021-04-02 00:00:00.000         2021-01-01
1966-05-04 00:00:00.000         1966-01-01

CodePudding user response：

Please use date_trunc

select date_trunc(year,'2021-04-02 00:00:00.000'::date);
 -------------------------------------------------- 
| DATE_TRUNC(YEAR,'2021-04-02 00:00:00.000'::DATE) |
|--------------------------------------------------|
| 2021-01-01                                       |
 --------------------------------------------------

CodePudding user response：

one way could be , I think what @Pankaj showed is better

select column1::timestamp as col1,        year(col1) as "YEAR"  
,year || '-' || '01'|| '-' ||'01' as dt
,to_date(dt,'YYYY-MM-DD')
from   values  
  ('2021-04-02 00:00:00.000') ,
('1966-05-04 00:00:00.000')

CodePudding user response：

Update such as this...

    update my_table
    set date_field = date_from_parts(extract(year from date_field), 1, 1)