DATEDIFF() SQL Function

In Structured Query Language (SQL), DATEDIFF() is the function that is used to determine the difference between two dates or intervals. It is a powerful function provided by SQL to manage time and date.

DATEDIFF() SQL Function

This article will teach us about DATEDIFF() SQL function, its syntax, needs, and examples.

Syntax of DATEDIFF() function:

In the above syntax, 'interval' defines the unit of time for which you want to find the difference. The value of the interval can be day, month, year, minute, hour, second, week, etc. 'startDate' is the starting date or time. 'endDate' is the ending date or time.

List of interval values with their abbreviations:

IntervalAbbreviations
Yearyear, yy, yyyy
Dayday, dd, d
Monthmonth, mm, m
Quarterquarter, qq, q
Weekweek, ww, wk
Nanosecondnanosecond, ns
Microsecondmicrosecond, us
Millisecondmillisecond, ms
Secondsecond, ss, s
Minuteminute, mi
Hourhour, hh

Needs of DATEDIFF() function:

  • The duration between events: It is used to calculate the duration between two events.
  • Scheduling and planning: It is used to find out the time left to complete specific tasks.
  • Age calculation: It helps to know the age of people based on their date of birth and current date.
  • Academic duration: It helps to find the length of the academic course.
  • Inventory management: It helps to trace the age or track the shelf life of the products.
  • Expiry and renewal dates: It helps to find out the remaining time for expiry and renewal dates to manage warranty, license, etc.
  • Travel duration: Travel websites use this function to calculate trip duration.
  • Loan calculations: Banking systems use this function to find loan periods and interest rates on the basis of the loan start and end dates.
  • Subscription services: It helps in handling the subscription services by calculating the remaining subscription time for the users.
  • Project management: Project management tools help to find the task durations, project completion dates, etc.
  • Employee leave management: HR systems use this function to track employee leave periods.

Examples of the DATEDIFF() function in Microsoft SQL Server:

We will understand the DATEDIFF() function properly with the help of examples.

Note: We will use the syntax in the context of Microsoft SQL Server. The syntax may differ from database to database.

Example 1:

Let us consider a table called 'Orders' with fields like orderId, orderDate, and deliveryDate.

Orders Table:

orderIdorderDatedeliveryDate
12023-05-282023-06-28
22023-07-252023-08-21
32023-06-202023-08-29
42023-08-012023-08-30
52023-06-192023-07-26

1. We will consider the above 'Orders' table and calculate the number of days between the orderDate and deliveryDate.

We will use the given statement to find the number of days:

We will use the given statement to find the number of years:

orderIdorderDatedeliveryDatedaysDifference
12023-05-282023-06-2831
22023-07-252023-08-2127
32023-06-202023-08-2970
42023-08-012023-08-3029
52023-06-192023-07-2637

2. We will consider the above 'Orders' table and calculate the number of years between the orderDate and deliveryDate.


orderIdorderDatedeliveryDateyearsDifference
12023-05-282023-06-280
22023-07-252023-08-210
32023-06-202023-08-290
42023-08-012023-08-300
52023-06-192023-07-260

3. We will consider the above 'Orders' table and calculate the number of months between the orderDate and deliveryDate.

We will use the given statement to find the number of months:


orderIdorderDatedeliveryDatemonthsDifference
12023-05-282023-06-281
22023-07-252023-08-211
32023-06-202023-08-292
42023-08-012023-08-300
52023-06-192023-07-261

Example 2:

Let us consider a table called 'Products' with fields like productId, productName, and releaseDate.

Products Table:

productIdproductNamereleaseDate
100Refrigerator2023-06-23
101Television2022-07-10
102Washing Machine2021-05-24
103Coffee Maker2020-04-20
104Printer2021-02-19

We will consider the above 'Products' table and calculate the number of months since releaseDate of the product.

We will use the given statement to find the number of months:

Let us consider a table called 'Employees' with fields like employeeId, Name, hireDate, and terminationDate.

productIdproductNamereleaseDatemonths_since_release
100Refrigerator2023-06-231
101Television2022-07-1012
102Washing Machine2021-05-2426
103Coffee Maker2020-04-2039
104Printer2021-02-1929

Example 3:

Employees Table:

employeeIdNamehireDateterminationDate
200Anshika2020-05-212022-05-25
201Shruti2021-04-122022-05-20
202Deepanshu2021-08-182023-09-11
203Milan2022-05-272023-06-14
204Harsh2023-01-202023-07-19

We will consider the above 'Employees' table and calculate the number of years worked by the employee.

We will use the given statement to find the number of years:


employeeIdNamehireDateterminationDateyears_worked
200Anshika2020-05-212022-05-252
201Shruti2021-04-122022-05-201
202Deepanshu2021-08-182023-09-112
203Milan2022-05-272023-06-141
204Harsh2023-01-202023-07-190

Example 4:

Let us consider a table called 'Meetings' with fields like meetingId, startTime, and endTime.

Meetings Table:

meetingIdstartTimeendTime
12023-07-18 09:002023-07-18 10:00
22023-07-18 11:002023-07-18 12:00
32023-07-19 10:002023-07-19 12:30
42023-07-20 11:002023-07-20 12:30
52023-07-20 05:002023-07-20 06:00

We will consider the above 'Meetings' table and calculate the duration of each meeting in minutes.

To find out how many minutes each meeting lasted, we will use the given statement:


meetingIdstartTimeendTimemeeting_duration_minutes
12023-07-18 09:002023-07-18 10:0060
22023-07-18 11:002023-07-18 12:0060
32023-07-19 10:002023-07-19 12:30150
42023-07-20 11:002023-07-20 12:3090
52023-07-20 05:002023-07-20 06:0060

Example 5:

Let us consider a table called 'Persons' which has fields like personId, Name, and birthDate.

Meetings Table:

personIdNamebirthDate
1Rahul2000-06-14
2Kajal2005-01-20
3Manish2010-02-25
4Raghav1999-11-27
5Nishant1996-05-10

We will consider the above 'Persons' table and calculate the age of each person in years.

We will use the given statement to find the age of each person:


personIdNamebirthDateage_years
1Rahul2000-06-1423
2Kajal2005-01-2018
3Manish2010-02-2513
4Raghav1999-11-2724
5Nishant1996-05-1027

Example 6:

Let us consider a table called 'Subscriptions' with fields like subscriptionId, startDate, and endDate.

Meetings Table:

subscriptionIdstartDateendDate
12022-11-132023-06-06
22022-12-102023-05-12
32023-04-052023-07-04
42023-05-022023-06-25
52023-03-182023-07-05

We will consider the above'Subscriptions' table and calculate the duration of each subscription in weeks.

We will use the given statement to find the duration of each subscription:


subscriptionIdstartDateendDateduration_weeks
12022-11-132023-06-0629
22022-12-102023-05-1222
32023-04-052023-07-0413
42023-05-022023-06-258
52023-03-182023-07-0516

Example 7:

Let us consider a table called 'Projects' with fields like projectId, startDate, and endDate.

Projects Table:

projectIdstartDateendDate
12023-01-102023-05-02
22023-03-092023-07-13
32023-04-162023-05-11
42023-02-112023-04-30
52023-06-022023-08-25

We will consider the above 'Projects' table and calculate the duration of each project in days.

We will use the given statement to find the duration of each project:


projectIdstartDateendDateproject_duration_days
12023-01-102023-05-02112
22023-03-092023-07-13126
32023-04-162023-05-1125
42023-02-112023-04-3078
52023-06-022023-08-2584

Example 8:

Let us consider a table called 'Logs', which has fields like logId, logTime, and previousLogTime.

Logs Table:

logIdlogTimepreviousLogTime
12023-07-21 10:302023-07-21 09:00
22023-07-21 09:302023-07-21 08:00
32023-07-21 11:302023-07-21 10:00
42023-07-21 05:302023-07-21 03:00
52023-07-21 07:002023-07-21 06:00

We will consider the above 'Logs' table and calculate the time difference in minutes between logTime, and previousLogTime.

We will use the given statement to find the time difference:


logIdlogTimepreviousLogTimetime_diff_minutes
12023-07-21 10:302023-07-21 09:0090
22023-07-21 09:302023-07-21 08:0090
32023-07-21 11:302023-07-21 10:0090
42023-07-21 05:302023-07-21 03:00150
52023-07-21 07:002023-07-21 06:0060

Example 9:

Let us consider a table called 'Users' with fields like userId, Name, and lastLoginDate.

Users Table:

userIdNamelastLoginDate
1Abhishek2023-06-19
2Vishal2023-05-01
3Avni2023-07-02
4Khushi2023-06-10
5Himanshu2023-07-05

We will consider the above 'Users' table and calculate the number of days since each user's last login.

We will use the given statement to find the number of days:


userIdNamelastLoginDatedays_since_last_login
1Abhishek2023-06-1934
2Vishal2023-05-0183
3Avni2023-07-0221
4Khushi2023-06-1043
5Himanshu2023-07-0518

Example 10:

Let us consider a table called 'Sales' with fields like saleId, saleDate, and previous_quarter_end_date.

Sales Table:

saleIdsaleDateprevious_quarter_end_date
12023-05-012023-04-02
22023-06-092023-05-06
32023-07-182023-06-17
42023-06-182023-05-06
52023-07-162023-06-10

We will consider the above 'Sales' table and calculate the number of quarters between the saleDate, and previous_quarter_end_date.

We will use the given statement to find the number of quarters:


saleIdsaleDateprevious_quarter_end_datequartersDifference
12023-05-012023-04-020
22023-06-092023-05-060
32023-07-182023-06-171
42023-06-182023-05-060
52023-07-162023-06-101

Conclusion:

In this article, we have learned about the DATEDIFF() SQL function, which is used to find the difference between two intervals. We have acquired knowledge about the needs of this function. We have seen various examples to understand the usage of the DATEDIFF() SQL function in real-world scenarios, such as finding the age of people, calculating the difference between two dates or times, and many more.


Next Topic#