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

user_9996914 0
Viewed 69

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
snowflake-cloud-data-platform datetime-format datetime sql
3 Answers

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                                       |
+--------------------------------------------------+
user_139153130

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')
user_112279190

Update such as this...

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