DATE FUNCTIONS IN SQL

Hi there. Today, we’ll be talking about date functions in SQL. Date functions are functions that help to format dates and carry out date-related calculations on your data. They are usually only effective on data types that are in date format on SQL. In this article, we will be exploring some of these date functions and how they are used. The syntax for this article will be that from Microsoft SQL Server.

The first date function we’ll look at is the DAY function. Like in the code below, the OrderDate column is placed in parentheses. This function returns the numerical day of the date in question. This means that if a customer placed an order on the 14th of October, 2021, the only thing that will be returned in the column labeled Day_of_Date is the number 14.

1
2
SELECT[CustomerID], DAY([OrderDate]) Day_of_Date
FROM[Sales].[SalesOrderHeader]

In the image below, the query returns the customer IDs along with the day they made their orders (which appear in integer format).

The next date function is the MONTH date function. This function also returns integers, but integers from 1 to 12 depending on the month of the year.

1
2
SELECT[CustomerID], MONTH([OrderDate]) Month_of_Date
FROM[Sales].[SalesOrderHeader]

In the image below the first couple of customers made orders in May. Therefore the Month_of_Date column returns the integer 5.

The YEAR function follows the same format as the DAY and MONTH functions. The function also returns an integer.

1
2
SELECT[CustomerID], YEAR([OrderDate]) Year_of_Date
FROM[Sales].[SalesOrderHeader]

The result of which looks like this:

We have gone through the day, month, and year functions. There is no built-in WEEK() function named as the day, month, and year functions are. But there is a way to derive that. In order to do so, we first have to look at another function.

The DateName function is a function that is used to derive the names of dates. For example, with the MONTH function, the results return integers, but with the DATENAME function, it returns the names of the month, like January or March.

1
2
SELECT[CustomerID], DATENAME(MONTH, [OrderDate]) Name_of_Month
FROM[Sales].[SalesOrderHeader]

The DATENAME function is divided into two arguments. The first argument is the month or day that you want to find relative to the date field or column in question. The date field, in this case [OrderDate], is the second argument. This should return the name of the month alongside the Customer ID like in the image below.

With a similar date function you can find the week of any date in question, like in the query below.

1
2
SELECT[CustomerID], DATEPART(WEEK, [OrderDate]) Week_of_Year
FROM[Sales].[SalesOrderHeader]

In this context, although the function WEEK can’t be used as a stand-alone function to derive the week out of any date, it can be used in conjunction with the DATEPART function to derive the week number of the year. The DATEPART function works similarly to the DATENAME function. The only difference is, instead of returning varchar, or the name of a month, it returns the integer of the date field in question. Because there are no names for the fifty-two weeks in a year, there’s no need to use the DATENAME function, although using it would return the same integer values. This piece of code obviously returns an integer value like in the image below.

The next function is the DATEDIFF function. This is used to find the difference between two dates. This function has three arguments. The first is to specify whether the difference between the two dates is in days, months, or years. If I’m looking for the difference in years between the OrderDate and this year (2021), the first argument takes the YEAR, to specify I want my answer in the year format. The second argument in the DATEDIFF function takes in the OrderDate column and takes the former of the two dates. The third argument takes in the latter of the two dates. For our example we are using the GETDATE() function for the third argument. The GETDATE() function is used in SQL date calculations to derive the current date. At the time this article is being written the current date is the 7th of October, 2021. A query using the DATEDIFF function to find the difference in dates between today’s date and the order date would look like this:

1
2
3
4
SELECT[CustomerID],
  YEAR([OrderDate])[Year of Order],
  DATEDIFF(YEAR, [OrderDate], GETDATE()) Date_Difference_In_Years
FROM[Sales].[SalesOrderHeader]

The result of the said query would look like this:

From the image above, the order date, aliased as the Year of Order, is 2011 for the first six customers in the result set. Because the date of this query is October 2021, the column that has the DATEDIFF function operating on it returns 10 (as in ten years) from the subtraction carried out on the column.

The last function we’ll look at is the DATEADD function. This function is used when adding intervals to or subtracting from a date field.

1
2
3
4
5
6
7
SELECT[CustomerID],
[OrderDate],
  DATEADD(YEAR, 5, [OrderDate])
'OrderDate+5 Years',
DATEADD(YEAR, -5, [OrderDate])
'OrderDate-5 Years'
FROM[Sales].[SalesOrderHeader]

In the query above, the DATEADD function is used to add five years to the OrderDate field in the third column and subtract five years from the OrderDate column in the fourth and last column.
There are three arguments. The first is for specifying whether a day, month or year is the date format the DATEADD function will be adding or subtracting. The second argument is the specific number of years, days, or months that will be added or subtracted, and the third argument is for the date field in question that the function will act on.
The only distinction between the two similar columns is that for subtraction a minus sign has to be in front of the second argument, the argument that contains the number of years, days, or months to be added or subtracted.
The result of this query would look like this:

In the image above, the DATEADD function has added five years to the OrderDate in the third column of the results query and subtracted five years from the OrderDate in the fourth column. Because the DATEADD function specified years, the hour, minute, second, and millisecond are all zeros.
The DATETIME format in SQL is a format that combines date and time features into one format, like YYYY-MM-DD hh:mm:ss[.nnn]. The TIMESTAMP format is quite similar to the DATETIME format. The main commonality of the two formats is that they combine DATE and TIME values together. The difference is in their range of values.

The DATETIME format has a range of values from ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’.

The TIMESTAMP format has a range of values from ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC.

UTC here stands for Coordinated Universal Time which is the main time standard used worldwide to regulate time, also known as GMT or Greenwich Mean Time.

A good example of this issue of timezones in SQL is the fact that I am currently writing this article within the West African Timezone (WAT). If I use the SELECT CURRENT_TIMESTAMP statement to find my current time, my SQL Server will return 2021-10-26 11:22:57.710 as a result. Take note of the 11 highlighted in red. That’s the hour stamp of the current time.

To see the current time from the UTC time zone itself, use the SELECT GETUTCDATE() statement. It returns the result 2021-10-26 10:22:57.710. Take note of the hour stamp (10) highlighted in red.

The reason for the disparity is that, using SQL, my server is deriving my time based on the timezone settings on my laptop. Because I’m in the WAT timezone, which is an additional hour (+1) from the UTC, there is a difference in the number of hours between the two timestamps. This is something to be conscious of when using timestamps/zones in SQL.

You can use the SELECT SYSDATETIMEOFFSET() statement to verify how many hours away from the UTC timezone your timezone is. The result of that, for me, within the WAT timezone would be 2021-10-26 11:22:57.2614427 +01:00. The +01:00 highlighted in red is to show that I am AHEAD of the UTC timezone by one hour.

In summary, these are a few date functions that can come in handy when answering business questions in SQL. It’s important to be familiar with basic date functions so that understanding more advanced functions and applying them within the right context will not be difficult.