Sometimes, you’ll be asked to show the next Friday after a given date in a report. Sometimes, it’s the next Monday. Let’s work thru how to do it in both SQL Server and Snowflake for comparison…
One of the two ways we’ll go over in SQL Server is using the DateFirst operator. This operator actually sets what SQL Server sees as the first day of the week.
The 6 after the DateFirst operator tells us that we want it to be Friday in this case.
We’re also using the DatePart function to determine the day of the week (dw) and subtracting that number from 7 to determine how many days to add to our original date.
Yes, that sounds like a lot of work to get to our answer.
Also – on a side note – the DateFirst operation is set at the session level. So, if you close and restart the session, it automatically starts with the server setting. If you leave the session open and do more work, you should reset the
You can also – if you don’t want to use the DateFirst operation – use this formula:
This time, we’re using remainders – found in the @DaysUntilFriday declaration. We’re using the DatePart function again. And we’re still doing lots of functions that we just have to remember if we want to make this happen.
Now, I don’t know about y’all, but I don’t have enough room in my brain for remembering things like this when I have more important things like AI, PowerBI Documentation, or dinosaurs.
In Snowflake, on the other hand, I have this nice function called NEXT_DAY- and its fun counterpart, PREVIOUS_DAY – that do all the behind the scenes work shown above.
Note that the NEXT_DAY and PREVIOUS_DAY functions both return Dates – not DateTimes. It also is to be noted that in the functions we used the column name of TheDate to hold our answer instead of using the function CURRENT_DATE() in our case. If we want to do some fun formula to determine TheDate – then it’s easier just to reference TheDate than to retype the formula.
And with that, we’ve learned about 2 new fun functions in Snowflake and the equivalent in SQL Server! Hopefully, you can use these soon in your work. Until next time my friends!