Daniel Calbimonte
Get age in SQL Server based on bithdate

SQL Subtract dates

November 7, 2022 by

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:

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.

Now, it is public how old am I.

Get age in T-SQL

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:

Get age in SQL Server based on bithdate

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.

Get age of employees

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:

t-sql get the oldest employee

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:

sql substract dates oldest person

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.

currency date table.

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:

Totoal months between max and min rate.

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])

Work orders that took longer.

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:

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.

minutes per user

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.

Daniel Calbimonte
Latest posts by Daniel Calbimonte (see all)
Azure, T-SQL

About Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases. 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

168 Views