Using DATEADD, DATEDIFF and DATEPART T-SQL Functions in Simple Terms

This article focuses on developing a basic understanding of how to use one of the most common Transact-SQL date functions: DATEADD, DATEDIFF, and DATEPART.

In this article, I also stressed the importance of properly using these date functions in daily date manipulations followed by some interesting scenarios in which these date functions can be used in a collaborative way to solve slightly complex date calculations.

Since these functions are primarily used in date manipulations, let us first try to understand what we mean by date manipulation.

Understanding Date Manipulation

The date-time values often need to be modified as per requirement, and the method of modifying or handling or controlling the date-time calculations is known as date manipulation.

We also refer to this situation (date manipulation) when a date value is read from a database and then modified before it gets stored again.

Customer-Order Scenario

An interesting example of date manipulation is a customer-order scenario, when an order placed by a customer is processed, the delivery date must be set 5 days ahead of the order date, so this means that a developer must use T-SQL date function(s) to manipulate (modify) the order date to calculate the delivery date.

Sample Daily Sales Report

A slightly complex example is when a business user runs a Daily Sales Report if it shows yesterday’s results.

For instance, if we run the Daily Sales Report on Sunday at 11:00, it will show us results based on Saturday, and if we run it on Saturday at 17:00, it will show us all the results on Friday, because the current day is not over yet and the most recent complete day available is yesterday. This is how most professional daily reports including financial reports are designed to run.

In this example, the current date is manipulated (modified) to get the previous date, which contains sales records for a complete day.

Implementing the Examples

Please keep the above examples in mind since we are going to implement these scenarios once we get a good understanding of using some of the most common date functions described in this article.

Understanding Date Functions

Let’s first look at some basic date functions that can help us meet date manipulation requirements, such as determining days between two dates (order date and delivery date), getting last week’s sales records based on current date or calculating the expected expiry date based on the production date and so on.

Since there are no hard and fast rules, we begin exploring DATEPART function first.

Using DATEPART Function

Simple Definition

DATEPART function is used to return a part of a given date in a numeric value.

The part can be the day of the date, month of the date, year of the date etc.

For example, we can use the DATEPART function to get the day of a given date to determine whether an order was placed on Sunday or not.

Another example is to get the month of a given date to be passed on to another date function for further processing.

Microsoft Definition

This function returns an integer representing the specified datepart of the specified date.

Compatibility

According to Microsoft documentation, this function is compatible with the following SQL Server versions:

1. SQL Server 2008 plus
2. Azure SQL Database
3. Azure SQL Data Warehouse
4. Parallel Data Warehouse

Syntax

DATEPART (datepart , date)

Example 1: Getting Year Part of the Date

Let us also define a certain date (OrderDate) to get its desired part (Day, Month, Year) using the DATEPART function.

To get Year of the order date, we simply pass YEAR followed by Order Date (@OrderDate) in the DATEPART function as follows:

-- Define Order Date
DECLARE @OrderDate DATETIME2='2017-01-11'

-- Getting Year of the Order Date
SELECT DATEPART(YEAR,@OrderDate) as Year_OrderDate

Example 2: Getting Month Part

If we are interested to know the month of the date then Month needs to be passed into the DATEPART function as follows:

-- Define Order Date
DECLARE @OrderDate DATETIME2='2017-01-11'

-- Getting Month of the Order Date
SELECT DATEPART(MONTH,@OrderDate) as Month_OrderDate

Example 3: Getting Day Part

To find the Day part of the date, simply pass DAY into the DATEPART function as follows:

-- Define Order Date
DECLARE @OrderDate DATETIME2='2017-01-11'

-- Getting Day of the Order Date
SELECT DATEPART(DAY,@OrderDate) as Day_OrderDate

Example 4: Getting Week Day Part

To get the Week Day part of the date, simply pass WEEKDAY into the DATEPART function as follows:

-- Define Order Date
DECLARE @OrderDate DATETIME2='2017-01-11'

-- Getting Week Day of the Order Date
SELECT DATEPART(WEEKDAY,@OrderDate) as WeekDay_OrderDate

We are getting 4, which is Wednesday starting from Sunday, which is 1.

Similarly, we can also get a Quarter, Hour, Minute, Second part of the date.

Let’s move on to the next date function.

Using DATEADD Function

Simple Definition

The DATEADD function is used to add or subtract a date.

For example, we can find out what the date will be after four days or four days before.

This is very handy in the scenarios where the expected date has to be calculated based on a given date such as membership expiry date must be exactly one year from the registration date.

Another example is to calculate the course end date which must be exactly two months after the course start date.

Microsoft Definition

This function adds a specified number value (as a signed integer) to a specified datepart of an input date value, and then returns that modified value.

Compatibility

According to Microsoft documentation, this function is compatible with the following SQL Server versions:

1. SQL Server 2008 plus
2. Azure SQL Database
3. Azure SQL Data Warehouse
4. Parallel Data Warehouse

Syntax

DATEADD (datepart, number, date)

Datepart is any part of the date such as day, month, year, weekday, hour etc.

Number is then number of the datepart (day, month, year etc.) to be added or subtracted

Date is a given date which needs to be added or subtracted using the DATEADD function

Example 1: Getting Next Year Date

Let us also define a certain date (Registration Date) which is going to be added or subtracted using the DATEADD function based on the requirements.

The next year date can be obtained by adding 1 to the Year datepart.

To get the next year from the registration date, we simply add DatePart Year followed by 1 followed by Registration Date (@RegistrationDate) in the DATEADD function as follows:

-- Define Registration Date
DECLARE @RegDate DATETIME2='2018-07-10'

-- Getting Next Year from registratoin date
SELECT DATEADD(YEAR,1,@RegDate) as NextYear_RegDate

Example 2: Getting Next Month Date

To get Next Month Date, pass the MONTH datepart to the DATEADD function followed by the number of months we want to add followed by the given date which is the registration date (RegDate) in our case.

-- Define Registration Date
DECLARE @RegDate DATETIME2='2018-07-10'
SELECT @RegDate AS RegDate -- Show Registration Date

-- Getting Next MONTH from the registratoin date
SELECT DATEADD(MONTH,1,@RegDate) as NextMonth_RegDate

Example 3: Getting Next Day Date

If the course begins on the following (next) day of registration, we need to pass DAY with 1 since the next day adds another day to the registration date, which is demonstrated as follows:

-- Define Registration Date
DECLARE @RegDate DATETIME2='2018-07-10'
SELECT @RegDate AS RegDate -- Show Registration Date

-- Getting Next DAY from registratoin date
SELECT DATEADD(DAY,1,@RegDate) as NextDAY_RegDate

Example 4: Setting up Daily Sales Report Date

Now let’s focus on a slightly complex scenario often used in the development of daily financial reports.

If we want to create a daily sales report, it should show yesterday’s data, since the current day is not yet complete and the most recent complete day is yesterday as discussed at the beginning of this article.

In the end, we also need to convert the yesterday’s Date Time into Date only value to make it easier for the report to cover the whole day.

To get yesterday’s date based on today’s date, we need to add “-1 day” to the current date as follows:

-- Define Current Date
DECLARE @CurrentDate DATETIME2=GETDATE()
SELECT @CurrentDate AS CurrentDate -- Show Registration Date

-- Getting Yesterday Date and Time from current date
SELECT DATEADD(DAY,-1,@CurrentDate) as YesterdayDateTime_CurrentDate

-- Converting Yesterday DateTime into Date only
SELECT CAST(DATEADD(DAY,-1,@CurrentDate) AS DATE)as YesterdayDateOnly_CurrentDate

Using DATEDIFF Function

Simple Definition

The DATEDIFF function is used to determine the difference in days, months, years, hours etc. between two dates.

For example, we can find out how many days have passed between two dates.

This is also very handy in the scenarios where we need to investigate the difference between the expected order delivery date and the actual order delivery date.

Another example is time tracking, which means understanding how many hours have been spent on a particular project since it began up till now.

Microsoft Definition

This function returns the count (as a signed integer value) of the specified datepart boundaries crossed between the specified startdate and enddate.

Compatibility

According to Microsoft documentation, this function is compatible with the following SQL Server versions:

1. SQL Server 2008 plus
2. Azure SQL Database
3. Azure SQL Data Warehouse
4. Parallel Data Warehouse

Syntax

DATEDIFF ( datepart , startdate , enddate )

Datepart is any part of the date such as day, month, year, weekday, hour etc.

Example 1: Understanding Difference in Days

Let us try to understand how the DATEDIFF function works.

If we try to find out the difference in days between 01 July 2018 and 02 July 2018, we will get 1 day, which means it subtracts the end date from the start date to get the difference:

SELECT DATEDIFF(DAY,'01 JULY 2018','02 JULY 2018') AS Days_Between_Two_Consecutive_Dates
SELECT DATEDIFF(DAY,'01 JULY 2018','03 JULY 2018') AS Days_Between_01_July_03_July

Example 2: Getting Days between Order and Delivery

Let’s define two different dates, Order Date and Delivery Date, which will be used to determine the difference in days, months, years, hours etc.

To find out the number of days between the order date and the delivery date, the DAY datepart is passed into the DATEDIFF function followed by Start Date (OrderDate) and End Date (DeliveryDate):

-- Define Order Date and Order Delivery Date
DECLARE @OrderDate DATETIME2='28 July 2018'
DECLARE @DeliveryDate DATETIME2='07 August 2018'

SELECT @OrderDate AS OrderDate,@DeliveryDate as DeliveryDate-- Show Order and Delivery Dates

-- Getting difference in days between order date and delivery date
SELECT DATEDIFF(DAY,@OrderDate,@DeliveryDate) as Days_Between_Order_and_Delivery

Example 3: Getting Project Hours (Time Tracking)

This is an interesting example of time tracking using DATEDIFF function.

For example, we are interested to know how many total days and days in hours and days in minutes   we spent on a particular project, and then we are going to first pass the DAY date part into the DATEDIFF function, where the start date is the date when project began and the end date is today’s date followed by passing HOUR and then passing MINUTE as follows:

-- Define Project Start Date
DECLARE @ProjectStartDate DATETIME2='10 Nov 2018'


SELECT @ProjectStartDate AS ProjectStartDate-- Show Project Start Date

-- Getting Number of days spent on the project so far
SELECT DATEDIFF(DAY,@ProjectStartDate,GETDATE()) as Project_Days_So_Far
-- Getting Number of hours spent on the project so far
SELECT DATEDIFF(HOUR,@ProjectStartDate,GETDATE()) as Project_Hours_So_Far
-- Getting Number of minutes spent on the project so far
SELECT DATEDIFF(MINUTE,@ProjectStartDate,GETDATE()) as Project_Minutes_So_Far

Congratulations, you have successfully learned to use DATEADD, DATEDIFF, and DATEPART T-SQL functions.

Things to Do

Now that you are familiar with some basic date functions, please challenge yourself by trying things mentioned below:

1. Please refer to my previous article Unit Testing Report Procedures – Jump to Start TDDD Part-4 to set up a sample SQLDevBlogReportTDD database, and then create a view to find out the number of months based on the Author registration date.
2. Please refer to my previous article Simplifying Unit Testing Main Stored Procedure Which Also Calls a Utility Procedure to create a sample SQLBookShop database and add the DeliveryDate column to the BookOrder table, and then create a new stored procedure ProcessOrder which uses the DATEADD function to add expected delivery five days after the order date.
3. Please have a look at my previous article Jump to Start Test-Driven Database Development (TDDD) – Part 3 and try to create a daily report using the DATEADD T-SQL function by modifying the data in the sample SQLDevBlogReportTDD database so that there is enough data to be displayed in the report.