CODEWITHSUNDEEP
snowflake-cloud-data-platform
user3840800
user3840800

Reputation: 71

How to get a weekly view from date field in snowflake

I have a small query mentioned below. Need to break down a date field "DAY" to monthly and weekly wise in snowflake.

Input:

DAY
-------
2022-06-09
2022-04-04

Output

DAY_MONTH
----------
2022-06-01
2022-04-01  Monthly wise--- Its done

Here I have used

 DATE_FROM_PARTS( YEAR(DAY), MONTH(DAY), 1) AS DAY_MONTH 

DAY_WEEK
----------
2022-06-06  
2022-04-04

They should be first day of working days like (Monday). How to do that for a weekly view?

Upvotes: 0

Views: 1842

Answers (1)

Greg Pavlik
Greg Pavlik

Reputation: 11076

I think you're looking for the date_trunc function:

set ts = '2022-07-07 11:14:00'::timestamp;

select date_trunc('DAY', $TS);
select date_trunc('WEEK', $TS);
select date_trunc('MONTH', $TS);

This is showing for a timestamp to show how it truncates to the day, but it works the same way for date types. Truncating to the week will start by the week_start parameter that's in effect (it will default to Monday as the start for this function):

https://docs.snowflake.com/en/sql-reference/parameters.html

Upvotes: 2

Related Questions