Introduction

Dealing with Dates in SQL has been quite fascinating for me. Often, I've come across newbies who find it quite challenging to understand the way dates work in SQL Server. Especially, when it comes to using different date functions related to formatting dates, extracting year, months from date, etc. We will look into some of the important DATE functions available in SQL Server - DATEADD, DATEDIFF, DATEPART, etc. with examples.

In this article, we will explore the following:

1. DATEADD function
   • Add or Subtract time periods (year/month/day) from a given date
2. DATEDIFF function
   • Some simple use cases of the DATEDIFF function
   • Combine DATEDIFF with DATEADD to calculate complex time periods
3. DATEPART function
   • Explore the DATEPART function along with some examples

DATEADD Function

The DateAdd function is used to add or subtract a specified amount of time period from a given date value.

Let me explain a simple use case for this. Let's assume you are working for an e-commerce company that ships orders to its customers which are placed online. The shipping is usually done within two days after the order is placed. Here, the ShippingDate can be calculated by adding two days to the OrderDate.

The syntax for the DATEADD function is as follows:

DATEADD(DATEPART, NUMBER, DATE)

The arguments accepted by the DATEADD function are as follows:

• DATEPART: It specifies which part of the date that we want to add or subtract (day, month, week, etc.)
• NUMBER: This is the number by which the Date value is to be increased or decreased. It must be an integer value.
• DATE: This the original date value on which all the calculations will be performed. It can be a Date, DateTime, DateTime2, SmallDateTime, DateTimeOffset, or Time value.

If we consider the same use case explained above, let us assume the order is placed on 15-Jan-2020. So, if we add two days, then the ShippingDate will be on 17-Jan-2020. This can be calculated as follows:

OrderDate = 2020-01-15
ShippingDate = DATEADD(DAY, 2, OrderDate)

There are various values for the DATEPART argument which are discussed below along with the short values in parenthesis.

• YEAR (yy, yyyy)
• QUARTER (qq, q)
• MONTH (mm, m)
• DAYOFYEAR (dy, y)
• DAY (dd, d)
• WEEK (wk, ww)
• WEEKDAY (dw, w)
• HOUR (hh)
• MINUTE (mi, n)
• SECOND (ss, s)
• MILLISECOND (ms)
• MICROSECOND (mcs)
• NANOSECOND (ns)

Examples

Let us now see some examples of how can we use the DATEADD function and achieve our desired results. Please note that, for some of the examples, I'll be using the GETDATE() function and for the others, I'll be using a hardcoded date value. As I'm writing this article on 04-Feb-2020, all the references will be taken accordingly.

YEAR

Returns the same DateTime as previous and next year.

SET NOCOUNT ON
SELECT GETDATE() AS Today
SELECT DATEADD(YEAR,1,GETDATE()) AS NextYear
SELECT DATEADD(YEAR,-1,GETDATE()) AS PreviousYear

MONTH

Returns the same DateTime as previous and next month.

SET NOCOUNT ON
SELECT GETDATE() AS Today
SELECT DATEADD(MONTH,1,GETDATE()) AS NextMonth
SELECT DATEADD(MONTH,-1,GETDATE()) AS PreviousMonth

HOUR

Returns the same DateTime as the previous and next hour.

SET NOCOUNT ON
SELECT GETDATE() AS Today
SELECT DATEADD(HOUR,1,GETDATE()) AS NextHour
SELECT DATEADD(HOUR,-1,GETDATE()) AS PreviousHour

Likewise, you can use all the other DATEPART arguments in the same way as used in these examples.

DATEDIFF Function

The DateDiff function is used to calculate the difference of time between two given dates or time values. It is used to count the number of years, months, days, minutes, seconds, etc. between a StartDate and an EndDate.

A simple use case for this can be taken as for an airline company; we need to calculate how long does a flight take to reach its destination after leaving the source. This is usually helpful in analyzing how many flights were in time, delayed or reached the arrival station earlier than expected.

The syntax for the DATEDIFF function is as follows:

DATEADD(DATEPART, STARTDATE, ENDDATE)

The arguments accepted by the DATEADD function are as follows:

• DATEPART: It specifies which part of the date that we want to add or subtract (day, month, week, etc.)
• STARTDATE: This is the beginning date from where the function will start calculating the period. It can be a Date, DateTime, DateTime2, SmallDateTime, DateTimeOffset, or Time value.
• ENDDATE: This is the end date until which the function will end calculating the period. It can be a Date, DateTime, DateTime2, SmallDateTime, DateTimeOffset, or Time value.

If we consider the use case as described above, let us assume that the flight left the Origin station at 10 am in the morning and reached its destination at 12 pm at noon. In such a case, the travel time will be calculated as 12 pm - 10 am = 2 hours. This can be calculated as follows:

StartTime = 10:00:00.000
EndTime = 12:00:00.000
TravelTime = DATEDIFF(HOUR, StartTime, EndTime)

Calculating Time Difference between two Periods

SET NOCOUNT ON
GO
SELECT DATEDIFF(HOUR,'10:00:00.000','12:00:00.000') AS TravelTimeInHours
SELECT DATEDIFF(MINUTE,'10:00:00.000','12:00:00.000') AS TravelTimeInMinutes

Calculating Age from DateOfBirth

This is another very useful scenario where BirthDates of different customers/users are stored in the database and we need to calculate the age based on the date the query is executed. As the age changes over time, it is not a good idea to insert direct values into the database directly. Even if we have a field for age, it needs to be periodically updated so that the age reflects correctly.

Let us consider the following dataset which has two users along with their DateOfBirths. We can calculate the age by using the DateDiff function. An important point to note here is that for the End Date we consider the Current Date from the system and the Start Date as the BirthDate.

SELECT 
[User]
,[DOB] 
,DATEDIFF(YEAR,[DOB],GETDATE()) AS AgeInYears
FROM [Users]

When you see the above calculation, you might notice that the calculation returns an inflated value of the age. For example, Adam has completed his 27th birthday in December 2019, however, the query is returning 28. This is because we are just taking the years into consideration while calculating the age, and this is not correct.

The most accurate way of calculating the age is to extract the difference in years from the DateOfBirth, as we did earlier and then add the number of years to the BirthDate. Finally, check if the date after adding the years is less or more than today. If it is more than today, subtract 1 from it.

Let's see this in action in a step-by-step manner.

1. Calculate the AgeInYears as we did previously. You can follow the same script as attached above for this step.
2. Add the AgeInYears to the DOB and store it in a new column ThisYearBirthDate.

   SELECT 
   [User]
   ,[DOB] 
   ,DATEDIFF(YEAR,[DOB],GETDATE()) AS AgeInYears
   ,DATEADD(YEAR,DATEDIFF(YEAR,[DOB],GETDATE()),[DOB]) AS ThisYearBirthDate
   FROM [Users]
   

   

3. Check if ThisYearBirthDate is greater than today. If yes, subtract 1 from it and return AdjustedAgeInYears.

   SELECT 
   [User]
   ,[DOB] 
   ,DATEDIFF(YEAR,[DOB],GETDATE()) AS AgeInYears
   ,DATEADD(YEAR,DATEDIFF(YEAR,[DOB],GETDATE()),[DOB]) AS ThisYearBirthDate
   ,DATEDIFF(YEAR,[DOB],GETDATE()) - 
   CASE WHEN 
   DATEADD(YEAR,DATEDIFF(YEAR,[DOB],GETDATE()),[DOB]) > GETDATE()
   THEN 1
   ELSE 0
   END AS AdjustedAgeInYears
   FROM [Users]
   

   

As you can see in the above script, the column AdjustedAgeInYears returns the correct completed age of both the users.

Calculating Start or End of a Time Period

Sometimes, we need to calculate the beginning date of a period, let's say, the beginning of this week, or the beginning of last month and so on. This is useful when analyzing financial reports where we need to calculate reports based on Year-To-Date, Month-To-Date, etc. These types of calculations can also be achieved using a combination of the DATEDIFF and DATEADD functions. For the examples below, I'll be using a hardcoded date value, rather than using the GETDATE function.

Calculating Start of a Time Period

DECLARE @Today DATE = '2019-12-26'
SELECT DATEADD(WEEK,DATEDIFF(WEEK,0,@Today),0) StartOfWeek
SELECT DATEADD(MONTH,DATEDIFF(MONTH,0,@Today),0) StartOfMonth
SELECT DATEADD(YEAR,DATEDIFF(YEAR,0,@Today),0) StartOfYear

You may notice in the above script that for DATEDIFF, instead of using a StartDate value, I have used "0" (zero). Well, this zero has a special meaning in SQL Server and it resolves to the date of "1900-01-01". So, when we use the value as zero, the database engine considers the StartDate as 1900-01-01 and calculates all the following periods accordingly. Similarly, for DATEADD, we provide the Date value as zero, such that all the calculations are performed from 1900-01-01.

The way, the above script works is first, we calculate the difference of a specific time period between the given date and the reference date (in this case, 1900-01-01). If the period is YEAR, then, the value would be 119. Once, we have this value, the next step is to add this to the reference year using the DATEADD function using the same time period. This will return the first value of that period irrespective of the year, month or quarter.

DECLARE @Today DATE = '2019-12-26'
SELECT DATEDIFF(YEAR,0,@Today) DifferenceInYears
SELECT DATEADD(YEAR,DATEDIFF(YEAR,0,@Today),0) StartOfYear

Calculating End of a Time Period

DECLARE @Today DATE = '2019-10-26'
SELECT DATEADD(MILLISECOND,-3,DATEADD(WEEK,DATEDIFF(WEEK,0,@Today)+1,0)) EndOfWeek
SELECT DATEADD(MILLISECOND,-3,DATEADD(MONTH,DATEDIFF(MONTH,0,@Today)+1,0)) EndOfMonth
SELECT DATEADD(MILLISECOND,-3,DATEADD(YEAR,DATEDIFF(YEAR,0,@Today)+1,0)) EndOfYear

This also works similar to the above, however, we add one period after calculating the difference from 1900-01-01 and then subtract 3 milliseconds from the total value. This gives us the previous time period with a millisecond value of up to 997. There is good documentation available on how rounding of DateTime works in SQL and why we subtract 3 milliseconds instead of just 1.

Sometimes, you might come across tables where date values are stored as DATE and not as a DATETIME. In such cases, when we need to calculate the end of a given period, you can use the following approach rather than using the milliseconds to subtract from the next day.

DECLARE @Today DATE = '2019-10-26'
SELECT CAST(DATEADD(DAY,-1,DATEADD(WEEK,DATEDIFF(WEEK,0,@Today)+1,0)) AS DATE) EndOfWeek
SELECT CAST(DATEADD(DAY,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,@Today)+1,0)) AS DATE) EndOfMonth
SELECT CAST(DATEADD(DAY,-1,DATEADD(YEAR,DATEDIFF(YEAR,0,@Today)+1,0)) AS DATE) EndOfYear

In the above example, while calculating the end period for week, you may notice that the EndOfWeek is usually a Sunday. However, this might not be the case always, and you might want to change that. You can set the start of weekday by using the DATEFIRST statement in SQL and this way it will tell the database engine from which weekday should it start calculating a new week. Let us now see how can we set the weekend day as Saturday, instead of Sunday and execute the query for EndOf Week again.

SET DATEFIRST 7;
/*
    Setting DATEFIRST value as 7 will return Saturday as end of week in this case
*/DECLARE @Today DATE = '2019-10-26'
SELECT CAST(DATEADD(dd, 7-(DATEPART(WEEKDAY, @Today)),DATEADD(dd, DATEDIFF(dd, 0, @Today), 0)) AS DATE) EndOfWeek

DATEPART Function

This is one of the most simple Date Functions used in SQL Server. It is used to extract the value of a specific DatePart from a given Date. The DateParts can be anything from year, month quarter, day, week, etc. To understand a use case, let us consider that we need to generate a Monthly Sales Report that will aggregate the gross sales for all the days of that month and aggregate the data.

The syntax for the DATEPART function is as follows:

DATEPART(DATEPART, DATE)

The arguments accepted by the DATEADD function are as follows:

• DATEPART: It specifies which part of the date that we want to add or subtract (day, month, week, etc.)
• DATE: This is the date value from which the part is to be extracted. It can be a Date, DateTime, DateTime2, SmallDateTime, DateTimeOffset, or Time value.

For the sake of this example, we will consider the WideWorldImportersDW database and generate a similar report.

Monthly Sales Report

USE [WideWorldImportersDW]
GO
SELECT
DATEPART(MONTH, [Invoice Date Key]) AS [Month]
,SUM([Total Including Tax]) [Total Including Tax]
FROM Fact.Sale
WHERE [Invoice Date Key] BETWEEN '2014-01-01' AND '2014-12-01'
GROUP BY
DATEPART(MONTH, [Invoice Date Key])
ORDER BY
DATEPART(MONTH, [Invoice Date Key])
GO

Yearly Sales Report

USE WideWorldImportersDW
GO
SELECT
DATEPART(YEAR, [Invoice Date Key]) AS [Year]
,SUM([Total Including Tax]) [Total Including Tax]
FROM Fact.Sale
GROUP BY
DATEPART(YEAR, [Invoice Date Key])
ORDER BY
DATEPART(YEAR, [Invoice Date Key])
GO

Likewise, the DATEPART function can also be used to extract other parts of the date like a quarter, week, hour, minute and so on.

Takeaway

This article discusses how to work with the DATE functions in SQL along with several examples of the same. For next steps please refer the following:

• Official Documentation from Microsoft
• DateAdd functions in SQL Server from Database Guide
• DateDiff Function in SQL
• DatePart Function in SQL