Common SQL date functions in Visual Studio

I use Visual Studio as a code editor a lot at work to connect with our data warehouse. It has its own downsides, but I have enjoyed it so far and in some aspects, it is better than SAS. One of the reasons why I prefer Visual Studio is that it has far simpler date formats than SAS. Due to a much smaller number of date formats, Visual Studio poses a lesser challenge in handling date functions compared to SAS. Today, I’ll discuss a few common SQL date functions in Visual Studio. This entry is not for those who are really experienced, but for someone who are like me a while ago and just started to code in Visual Studio.

Declare, set and print a date

These functions are pretty straightforward. There is no need to explain further. As you can see, “date” as a format is way simpler than SAS’ formats such as date9. or ddmmyy10. The print function helps to double check whether you declare and set the right format. You can either hard-code a value or use built-in functions like getdate()

Add/subtract days from a specific date

What if you want to go forward or backwards from a specific date? Dateadd function is your friend

Get to the end or beginning of a month

What if you want to get the end or beginning of a month? To get month end of a date, use EOMONTH. You can also get the end of a month in the future or in the past. See below for examples

Let’s say today is 14th June 2020, if you want to get the first month of June and May, you can follow the below

Convert variables between integers and dates

Sometimes, a date attribute is in integer format. How to convert back and forth?

Convert a date into formats like 2020Q2 or 2020-06

Continue to build on today as of 14th Jun 2020, what if you want to convert that to a variable looking like this: 2020Q2 or 2020-06?

I hope those examples are helpful. They may look easy, but there is one thing I have learned from coding and blogging for more than 2 years: there is always someone out there who needs to see specific examples in addition to instructions on what functions to use. I wrote about how to create a map with Mapbox and how to create a hover effect on Mapbox. It’s nothing sophisticated, but it has generated quite a bit of traffic for me even after 2 years.