This is the end… of the month.

In SQL Server, we’re used to finding the end of the month via a few different methods. We can always use the DateAdd and DateDiff functions to get our data – which sometimes takes a bit of work – or we can use the EOMonth function.

Notice that the DateAdd and DateDiff functions return a datetime data type, while EOMonth only returns a date. This can be important depending upon what results you wish to provide or just a simple reminder to convert it to the “correct” data type later on.

In Snowflake, there is no EOMonth function. There is only Zuul.

Wait, sorry. The Keymaster is looking for Zuul.

In Snowflake, there are a few ways to get to the end of the month for a date.

Notice the EasyWay here. It’s just using the LAST_DAY function to get the last day of the month. If we want to get the last day of the week, we will use the optional parameter here – date_part – to tell Snowflake over what time period we want to get the last of. For example, we can get year, quarter, month, or week.

Notice that in this case of the week, the last day of the week is Sunday.

The other function that we can use in Snowflake is the TIME_SLICE function. It allows us to define our own periods of time – for example, 3 weeks or 6 months – and find the last date in that period.

For the above, I have taken a 3-week time slice. The start of it – according to Snowflake – is 1 week before today and it ends 2 weeks after today. (I’m sure there’s a good reason for this grouping, but it stays somewhat consistent.)

Shown here is a 6-month time slice. Notice again, the month that we are currently in and the rest of the months fall afterward. For those of you playing at home, if the date provided is the first of the month then it sets that as the first month and everything comes afterward.

So, my word of advice when dealing with the TIME_SLICE function is to always look at the start and end of the period to make sure it’s the values you believe you should have. Otherwise, it may not give you the answers you seek.

And so, with that, we have come to the end of yet another post. Lots of new functions to play with and try to use in our daily work with Snowflake. Until then…