When you work with data, you’ll probably need to work with dates at least once a month. That is the nature of the beast. Today, let’s compare working with them in SQL Server and Snowflake. I want to focus only on adding and subtracting months when provided with a specific day.
In SQL Server, you get used to one of my favorite functions – DATEADD.
We are simply adding 1 month to the date 2/28/2023. Notice that it is the end of the month for February 2023. We nicely asked SQL Server to add 1 month to the date. SQL Server says that the answer is 3/28/2023.
SQL Server added 1 to the month and said that’s the answer. Okay… Not wrong, but what if I needed the new result to be at the end of the month as well? Now I have to add another function to my query… the all-powerful EOMONTH function!
Now, let’s do the same thing in Snowflake!
Well, at least it’s no worse in Snowflake… Same answer as SQL Server. In Snowflake, we use the LAST_DAY function to do the same thing – find the end of the month.
Now, as a data engineer – I despise typing any more than I just have to. Let’s see if I can do something to drop the amount of typing I need to do here.
In Snowflake, there is a function called ADD_MONTHS that can reduce all of the above to one small function call. Let’s see it in action, shall we?
For those of you who haven’t seen the “::date” part above – that’s just forcing it to show the data as a date data type instead of the datetime data type it will normally see things in.
Now, this command also works the same when subtracting dates as well. It keeps the date at the end of the month.
DATEADD in SQL Server or Snowflake does a great job – just not the end of the month that we get with ADD_MONTHS.
My lesson today is once you know how you need your dates to show then you can pick which of the date functions you truly need in Snowflake. And as always, it’s nice to have different ways to get to the same answer.
Until next time my friends, this is…