Understanding DateDiff in SQL

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');

DateDiffInSQL_1

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

FREE Course: Introduction to Data Analytics

Mastery to Data Analytics Basics is a Click Away!Start Learning
FREE Course: Introduction to Data Analytics

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;

DateDiffInSQL_2

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;

DateDiffInSQL_3

Code:

SELECT DATEDIFF(yy, '2010/05/10', '2010/10/21')AS No_of_year;

DateDiffInSQL_4

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;

DateDiffInSQL_5

Code:

SELECT DATEDIFF(day, '2021/01/01', '2019/06/1')AS No_Of_days;

DateDiffInSQL_6

Code:

SELECT DATEDIFF(hour, '2016/06/30 12:00', '2018/10/18 12:45') AS No_of_hours;

DateDiffInSQL_7

Code:

SELECT DATEDIFF(minute, '2015/04/02 09:00', '2015/04/08 11:55')AS No_of_minutes;

DateDiffInSQL_8

Post Graduate Program in Business Analysis

In partnership with Purdue UniversityVIEW COURSE
Post Graduate Program in Business Analysis

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.

/DateDiffInSQL_9

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

DateDiffInSQL_10

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

DateDiffInSQL_11.

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;

DateDiffInSQL_12

More Example:

/DateDiffInSQL_13

DateDiffInSQL_14

Gain expertise in the latest Business analytics tools and techniques with the Business Analyst Certification Program. Enroll now!

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!

About the Author

SimplilearnSimplilearn

Simplilearn is one of the world’s leading providers of online training for Digital Marketing, Cloud Computing, Project Management, Data Science, IT, Software Development, and many other emerging technologies.

View More
  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.