In this article, we will show how to subtract dates using SQL Server. This article will be a learn-by-example article with a problem and a solution. But first, I will add some theory to understand the syntax of the DATEDIFF function which is the base of this article.
Introduction
To summarize, we will cover the following topics.
- A brief summary of the DATEDIFF usage, syntaxis.
- How to get my age, using my birthdate.
- How to get the age of employees using a table.
- How to get the oldest employee in the company
- How to get the number of months between the best and the worst average price between the euros and the dollars.
- How to get the order id of the order which took more days.
- How to get the number of minutes worked by day of some employees.
A summary of the DATEDIFF usage, syntaxis
For the subtract dates, we use the DATEDIFF which finds the difference between 2 dates.
The syntax is simple:
|
1 2 3 |
DATEDIFF(dateunit,startdate,enddate) |
Where dateunit can be a year, quarter, month, dayofyear, day, week, hour, minute, second, millisecond, microsecond, or even nanosecond.
Let’s look at some examples.
How to get my age, using my birthdate
Let’s start with a simple and classic example. I have a birthdate and we want to know how old am I. My age will be public now. It was a secret that I kept for years.
|
1 2 3 |
SELECT DATEDIFF ( YEAR , '03-19-1979' , getdate() ) as myage |
Now, it is public how old am I.
The DATEDIFF function will return the number of years between my birthday (03-19-1979) and the current date (getdate). You can get the difference in years, months, days, and so on.
How to get the age of employees using a table using SQL subtract dates
Using the same concept, we will do the same in the Employee table from the Adventureworks database. If you do not have the Adventureworks, download it here:
Install and configure the AdventureWorks2016 sample database
The query used to get the age and loginid of the employees is the following:
|
1 2 3 4 |
SELECT DATEDIFF ( YEAR ,BirthDate , getdate() ) as age,LoginID FROM [HumanResources].[Employee] |
We are using the Employee database to get the BirthDate and then we find the difference between the current date and the birthdate.
Unfortunately, you can only get the loginID from the employee table, if you want to have the first name and last name, you will need to join the person.person table.
|
1 2 3 4 5 6 7 |
SELECT DATEDIFF ( YEAR ,BirthDate , getdate() ) as age,LoginID, P.FirstName,P.LastName FROM [HumanResources].[Employee] E JOIN [Person].[Person] P ON E.BusinessEntityID=P.[BusinessEntityID] |
How to get the oldest employee in the company using SQL subtract dates
If we want to get the oldest person in the company, we could do it with the following query:
|
1 2 3 4 5 |
SELECT TOP 1 WITH TIES DATEDIFF ( YEAR ,BirthDate , getdate() ) as age,LoginID FROM [HumanResources].[Employee] ORDER BY DATEDIFF ( YEAR ,BirthDate , getdate() ) DESC |
As you can see, Stephen is 70 and is the oldest employee at our table.
The select TOP 1 WITH TIES will find all the oldest persons including ties if any and we are ordering the DATEDIFF in DESC order to get the oldest one. However, if your table has several millions of rows, doing and ORDER BY may be very expensive for performance.
If you want to read more about performance problems by the ORDER BY, we encourage you to read our article related:
Instead, you can use the following alternative that does not require the order by:
|
1 2 3 4 5 6 |
SELECT LoginID as age FROM [HumanResources].[Employee] WHERE DATEDIFF ( YEAR ,BirthDate , getdate() ) = (SELECT MAX(DATEDIFF ( YEAR ,BirthDate , getdate())) FROM [HumanResources].[Employee]) |
The query is simply finding the LoginID of the person with the Maximum number of years.
How to get the number of months between the best and the worst average price between the euros and the dollars using SQL subtract dates
In this new example, we will use the CurencyRateDate table from the Adventureworks Database. Here you have a sample of the data.
We will find the difference in months between the minimum average rate and the maximum average rate. The syntax for this query is the following:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT DATEDIFF ( MONTH, ( SELECT CurrencyRateDate FROM [Sales].[CurrencyRate] WHERE AverageRate = (SELECT MIN(AverageRate) FROM [Sales].[CurrencyRate] ) ), ( SELECT CurrencyRateDate FROM [Sales].[CurrencyRate] WHERE AverageRate = (SELECT MAX(AverageRate) FROM [Sales].[CurrencyRate] ) ) ) MONTHS |
As you can see, it took 31 months (2 years and 7 months) to have the maximum and minimum average price for the EUR to USD rate price.
We got the date of the maximum average rate value and the minimum and then calculate the difference in months.
How to get the order id of the order which took more days using SQL subtract dates
We will now work with the Adventureworks WorkOrder table.
SELECT workorderid, DATEDIFF(day,StartDate,EndDate) totalTime FROM [Production].[WorkOrder] WHERE DATEDIFF(day,StartDate,EndDate)= (SELECT MAX(DATEDIFF(day,StartDate,EndDate)) FROM [Production].[WorkOrder])
The orders took longer took 32 days to be completed. You have the Word Order IDs to check them.
We will use the StartDate and EndDate columns with the DATEDIFF function to calculate the number of days spent.
To calculate the work order ID that took longer, in the WHERE clause we are comparing our WorkOrder time with the longest one in the table using the MAX function.
How to get the number of minutes worked by day of some employees using SQL subtract dates
In this example, we will calculate the total minutes worked by 2 customers. For this purpose, we will create a table named WorkTime. This table will have the data of 2 employees including the time that he enters the office and the exit time (start time and end time).
Here you have the script, to generate the table with data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
CREATE TABLE [dbo].[worktime]( [loginid] [int] NULL, [loginname] [nvarchar](30) NULL, [startDate] [datetime] NULL, [endDate] [datetime] NULL ) ON [PRIMARY] GO INSERT [dbo].[worktime] ([loginid], [loginname], [startDate], [endDate]) VALUES (1, N'Darthby', CAST(N'2021-07-12T08:01:34.000' AS DateTime), CAST(N'2021-07-12T10:15:34.000' AS DateTime)) INSERT [dbo].[worktime] ([loginid], [loginname], [startDate], [endDate]) VALUES (1, N'Darthby', CAST(N'2021-07-12T11:21:11.000' AS DateTime), CAST(N'2021-07-12T14:21:11.000' AS DateTime)) INSERT [dbo].[worktime] ([loginid], [loginname], [startDate], [endDate]) VALUES (1, N'Darthby', CAST(N'2021-07-12T15:41:14.000' AS DateTime), CAST(N'2021-07-12T19:31:11.000' AS DateTime)) INSERT [dbo].[worktime] ([loginid], [loginname], [startDate], [endDate]) VALUES (1, N'Darthby', CAST(N'2021-07-13T07:51:34.000' AS DateTime), CAST(N'2021-07-13T10:15:34.000' AS DateTime)) INSERT [dbo].[worktime] ([loginid], [loginname], [startDate], [endDate]) VALUES (1, N'Darthby', CAST(N'2021-07-13T11:31:11.000' AS DateTime), CAST(N'2021-07-13T13:20:11.000' AS DateTime)) INSERT [dbo].[worktime] ([loginid], [loginname], [startDate], [endDate]) VALUES (2, N'Depry', CAST(N'2021-07-12T08:05:34.000' AS DateTime), CAST(N'2021-07-12T11:15:34.000' AS DateTime)) INSERT [dbo].[worktime] ([loginid], [loginname], [startDate], [endDate]) VALUES (2, N'Depry', CAST(N'2021-07-12T11:21:11.000' AS DateTime), CAST(N'2021-07-12T13:21:11.000' AS DateTime)) INSERT [dbo].[worktime] ([loginid], [loginname], [startDate], [endDate]) VALUES (2, N'Depry', CAST(N'2021-07-12T15:31:14.000' AS DateTime), CAST(N'2021-07-12T18:31:11.000' AS DateTime)) INSERT [dbo].[worktime] ([loginid], [loginname], [startDate], [endDate]) VALUES (2, N'Depry', CAST(N'2021-07-13T08:51:34.000' AS DateTime), CAST(N'2021-07-13T10:35:34.000' AS DateTime)) INSERT [dbo].[worktime] ([loginid], [loginname], [startDate], [endDate]) VALUES (2, N'Depry', CAST(N'2021-07-13T12:31:11.000' AS DateTime), CAST(N'2021-07-13T13:26:11.000' AS DateTime)) INSERT [dbo].[worktime] ([loginid], [loginname], [startDate], [endDate]) VALUES (2, N'Depry', CAST(N'2021-07-13T14:21:14.000' AS DateTime), CAST(N'2021-07-13T18:39:11.000' AS DateTime)) INSERT [dbo].[worktime] ([loginid], [loginname], [startDate], [endDate]) VALUES (1, N'Darthby', CAST(N'2021-07-13T14:31:14.000' AS DateTime), CAST(N'2021-07-13T18:31:11.000' AS DateTime)) GO |
To get the total number of minutes per day, you can use the following query. We SUM all the start date and end date entries per day, we group by using the dd-MM-yyyy format to group the total minutes per day.
|
1 2 3 4 5 6 7 8 9 10 |
SELECT SUM(DATEDIFF( MINUTE, [startDate], [endDate])) minutes, FORMAT(startDate,'dd-MM-yyyy') day, loginname FROM [AdventureWorks2019].[dbo].[worktime] GROUP BY FORMAT(startDate,'dd-MM-yyyy'),loginname |
As you can see in the results, Darthby works harder than Depry. He worked 544 minutes (9 hours, 4 minutes) on 12-07 and 490 minutes on 12-08 (8 hours, 13 minutes).
On the other hand, Depry worked 490 minutes on 12-07 (8 hours, 10 minutes) and 417 minutes on 13-01 (6 hours 57 minutes).
Conclusion
In this article, we learned how to do SQL subtract dates using the DATEDIFF function. The datediff function can return the difference between two dates in days, months, years, minutes, etc.
We learned with examples, how to get information. We learned how to get work orders that took longer, get the oldest employees, the time between the maximum and minimum average price of the eur/USD currency price, and more.
He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams.
He also helps with translating SQLShack articles to Spanish
View all posts by Daniel Calbimonte
- PostgreSQL tutorial to create a user - November 12, 2023
- PostgreSQL Tutorial for beginners - April 6, 2023
- PSQL stored procedures overview and examples - February 14, 2023