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

Question

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!

score 14 · Answer 1 · edited Oct 13 '22 at 21:43

14

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

SELECT DATE_TRUNC(month, CURRENT_DATE()) AS first_day_of_month;

edited Oct 13 '22 at 21:43

Marco Roy

4,004
7
34
50

answered Sep 01 '20 at 19:59

GMB

216,147
25
84
135

GMB's answer is the most appropiate one, I am also sharing the entire SQL that you can use select date_trunc('MONTH', to_date('2014-02-17')) as FIRST_DAY_MONTH; – Rajib Deb Sep 01 '20 at 20:13

score 0 · Answer 2 · answered Sep 01 '20 at 20:04

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)

score 0 · Accepted Answer · answered Sep 28 '21 at 20:20

0

This is another option:

SELECT TRUNCTIMESTAMPTOMONTH(CURRENT_DATE());

answered Sep 28 '21 at 20:20

Praveen Kumar B

76
1
4

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

3 Answers3