DATEDIFF() in SQL
The DATEDIFF() function compares two dates and returns the difference. The DATEDIFF() function is specifically used to measure the difference between two dates in years, months, weeks, and so on.
This function may or may not return the original date. It returns the number of times it crossed the defined date part boundaries between the start and end dates (as a signed integer value).
Syntax:
DATEDIFF(date_part,start_date_value1, end_date_value2);
Date_part is nothing but a month, year, or day. In this formal difference, you would find that the result.start_date_value1 is the value of the starting date, and the end_date_value2 is the value of the ending date of a finding.
Code:
SELECT DATEDIFF(month,'2011-03-07' , '2021-06-24');
In this above example, you can find the number of months between the date of starting and ending. From the inputs you got there are 123 months between the date of 07/03/2011 to 24/3/2021.
You can even find the number of hours, minutes, seconds, and so on in terms of details in between the two dates, with the help of DATEDIFF().
There are many arguments associated with the DATEDIFF function in SQL. They are mentioned below.
DATEDIFF() Arguments:
The date part is the portion of a date that you want to compare with the end date and start date, like a year, a fifth, a month, or a week. In the table below, you'll find the valid date bits.
The dates to be compared are the start date and end date. They need to be converted to DATE, DATETIME, DATETIMEOFFSET, DATETIME2, SMALLATETIME, or TIME values.
There are almost three arguments in the DATEDIFF() function in SQL.
- DATEPART
- Starting DATE
- Ending DATE
Starting DATE/Ending DATE Argument
The starting DATE and ending DATE are common to follow the format to specify the values. For example, the date should be as YYYY-MM-DD Sec:MIN:Hour.
And the format of these dates may be:
- time
- date
- datetime
- datetime2
- datetimeoffset
- smalldatetime
DATE PART as Argument:
The principal argument in the DATEDIFF function is concerning DATEPART.
|
DATEPART |
Abbreviation |
|
year |
yy, yyyy |
|
quarter |
qq, q |
|
month |
mm, m |
|
dayofyear |
dy, y |
|
day |
dd, d |
|
week |
wk, ww |
|
hour |
hh |
|
minute |
mi, n |
|
second |
ss, s |
|
millisecond |
ms |
|
microsecond |
mcs |
|
nanosecond |
ns |
The DATEDIFF() function returns an integer value that represents the difference between the start date and end date, with the date part as the unit. If the result is out of range for the integer (-2,147,483,647), the DATEDIFF() function returns an error. Here, the DATEDIFF BIG() function should be used instead.
Two Ways to Use DATEDIFF() Function in SQL:
The first one is to find the differences between the two date values. In this function, you have to compare the two input data as date and value.
Another method is to find the differences between the two columns of data from the specified table attributes. It may return the result into the table of data.
Use of DATEDIFF() to find the differences between two date values.
In this type, the DATEDIFF() function is used to find the number of days or years or any other count between the two DATE values. This indicates the query’s starting date & ending date.
Example:
Code:
SELECT DATEDIFF(year, '2000/05/8', '2018/05/28')AS no_of_years;
In this above example, you calculated the number of years in between the given two inputs of dates. Then, the result will be named as no_of_year.
Code:
SELECT DATEDIFF(yyyy, '2011/11/18', '2016/03/2')AS total_Year;
Code:
SELECT DATEDIFF(yy, '2010/05/10', '2010/10/21')AS No_of_year;
All the above statements are used to find the years between the two dates. This has been initiated by the user. And you also saved the result into a specific name of data.
Code:
SELECT DATEDIFF(month, '2016/05/15', '2009/06/13')AS Months;
Code:
SELECT DATEDIFF(day, '2021/01/01', '2019/06/1')AS No_Of_days;
Code:
SELECT DATEDIFF(hour, '2016/06/30 12:00', '2018/10/18 12:45') AS No_of_hours;
Code:
SELECT DATEDIFF(minute, '2015/04/02 09:00', '2015/04/08 11:55')AS No_of_minutes;
Use of the DATEDIFF () to Find the Differences Between the Two Tables Column Values:
The DATEDIFF() function is used in the following example to compare the requested delivery date with the ship date in days. This determines if the order is on time or late.
To create the table, use emp_data_table for storing the employee details. That is emp_no,emp_name, and date of joining as st_date and date of resigning is en_date.
This table helps you to find the number of years worked in the same organization.
The table has been created successfully. Next, insert the records into the table with the proper input types. Almost insert the five and above records to a table.
Code:
insert into emp_data_table values(101,'mann','2001-01-01','2020-03-19')
insert into emp_data_table values(102,'barpara','2011-11-19','2021-03-10')
insert into emp_data_table values(103,'ven','2015-06-06','2019-11-11')
insert into emp_data_table values(104,'john','2015-09-19','2020-03-11')
insert into emp_data_table values(105,'ann','2001-01-01','2020-03-19')
select * from emp_data_table
Then, find the number of working experience from the following table. The data has been calculated with the help of a DATEDIFF() function in SQL.
SELECT DATEDIFF(yy,st_date,en_date) AS Total_year_of_Experience from emp_data_table;
The output is to display the number of years of experience for every employee from the above-mentioned table.
Result
Total_Year_of_Experience
19
10
4
5
19
Likewise, you can easily find the number of months and minutes or hours and so on. Next, below is the code calculated for the number of days between the given two dates of inputs.
Code:
SELECT DATEDIFF(DAY,st_date,en_date) AS Total_year_of_Experience FROM emp_data_table;
More Example:
Conclusion:
The DATEDIFF SQL Server feature calculates the difference between two date and time values in units of a specific element (i.e. year, month, day), but only for that element and higher elements in the time hierarchy–not for lower elements.
You can use either Day of year ("y") or Day of month ("m") to measure the number of days between date1 and date2 ("d"). DateDiff returns the number of weeks between the two dates when the interval is Weekday ("w"). DateDiff counts the number of Mondays before date2 if date1 is a Monday.
Finally, from the above examples, you can understand the process and uses of a DATEDIFF() function in SQL. It is used to find the difference between the two days. It may be calculated from the two days, such as two inputs or two columns.
Gain expertise in SQL programming language by joining our Simplilearn's SQL Certification Training Course. This SQL certification course will teach you everything you need to know about SQL databases and how to integrate them into your applications. Learn how to properly organise your database, write successful SQL statements and clauses, and scale your SQL database. This course covers SQL basics, all related query tools and SQL commands, an industry-recognized course completion certificate, and lifetime access to self-paced learning.
Despite the fact that SQL is an old programming language, it is still very useful today as businesses around the world gather large quantities of data. Mastering SQL, one of the most in-demand engineering skills, will expand your horizons.
Database and relational management, query tools and SQL commands, aggregate functions, group by clause, tables and joins, subqueries, data manipulation, transaction control, views, and procedures are only a few of the skills covered.
Start learning today's most in-demand skills for free. This course focuses on the development of strong core skills that will help you succeed in your career. You will be taught by experts in the region. Get access to more than 300 job-ready skills in today's hottest fields. While working or studying, learn from anywhere, on any laptop. There are free guides available on a variety of career paths, interview tips, salaries, and more.
Have any questions for us? Leave them in the comments section of this article, and our experts will get back to you on them, as soon as possible!