How to truncate a date to the first day of the month in Snowflake?

user_7916067 0
Viewed 4610

I am not able to find any good conversion code to get 2014-02-17 into 2014-02-01 without having to use concatenation and a ton of formatting. I wonder if someone can help me find a good command to achieve this. Thanks!

snowflake-cloud-data-platform date-arithmetic datetime sql
3 Answers

Snowflake supports date_trunc() for datatypes DATE, TIME, and TIMESTAMP:

date_trunc('month', mydate)
user_106767160

Sounds like you're working with strings. If that's the case and they'll always be in the format 'yyyy-MM-dd', you can just take the first 8 characters and add '01':

left(MyStringValue, 8) + '01'

If you're working with date fields, I like the trick of doing datediff to get the months from 0, then use dateadd with 0 to get back to the first of the month:

dateadd(month, datediff(month, 0, MyDateValue), 0)
user_3697920

This is another option:

SELECT TRUNCTIMESTAMPTOMONTH(CURRENT_DATE());

user_83729740
Related