DATEDIFF function in SQL Server

DATEDIFF in SQL Server What is that? How to use the DATEDIFF function in SQL Server how? Let's find out with Quantrimang.com!

The programming world is extremely diverse with many interesting languages ​​for you to learn and learn. From just lines of code, you can create extremely useful programs and applications.

If you want to develop a career in this industry, you must definitely know how to use SQL Server. Mastering it, you can easily manage the database when programming.

Learning SQL Server is basically not difficult. Let's start with the most necessary knowledge. Quantrimang.com has a learning roadmap available for you to refer to. In this article, we will learn about it together DATEDIFF function in SQL Server.

Describe

DATEDIFF in SQL Server is a basic function used to perform calculations based on dates. It returns an integer value in date units such as year, month, day, minute and second. The official definition of Datediff SQL Server is to calculate the difference between two dates.

The DATEDIFF SQL Server function has the following outstanding features:

• Used to find the difference between two dates.
• Belongs to the Date function category.
• Interval – time interval, first value of date and second value of date are the 3 parameters accepted by this function.
• The time can be included in the interval section along with the date value portion of this function.

Syntax

To use the DATEDIFF function in SQL Server, we use the following syntax:

DATEDIFF(dangthoigian, thoigian1, thoigian2)

Parameter:

• dangthoigian: time format used to calculate the difference between duration1 and duration2. It can be one of the following values:
  

  Value	Explain
  year, yyyy, yy	Year
  quarter, qq, q	Quarterly
  month, mm, m	Month
  dayofyear	Day of the year
  day, dy, y	Day
  week, ww, wk	Week
  weekday, dw, w	Day of the week
  hours, hh	Hour
  minute, mi, n	Minute
  second, ss, s	Second
  millisecond, ms	Milliseconds

• time1, time2: time period to calculate the difference.

Note:

• DATEDIFF can be used in the following versions of SQL Server: SQL Server 2017, SQL Server 2016, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005.

For example

Take a look and explore some examples of the DATEDIFF function in SQL Server.

SELECT DATEDIFF(year, '2019/04/28', '2021/04/28');
Result: 2
SELECT DATEDIFF(yyyy, '2019/04/28', '2021/04/28');
Result: 2
SELECT DATEDIFF(yy, '2019/04/28', '2021/04/28');
Result: 2
SELECT DATEDIFF(month, '2019/01/01', '2019/04/28');
Result: 3
SELECT DATEDIFF(day, '2019/01/01', '2019/04/28');
Result: 117
SELECT DATEDIFF(hour, '2019/04/28 08:00', '2019/04/28 10:45');
Result: 2
SELECT DATEDIFF(minute, '2019/04/28 08:00', '2019/04/28 10:45');
Result: 165

Use the DATEDIFF() function to compare the difference between two date values

This example uses the DATEDIFF function to compare the difference between two dates in different parts:

DECLARE 
    @start_dt DATETIME2= '2019-12-31 23:59:59.9999999', 
    @end_dt DATETIME2= '2020-01-01 00:00:00.0000000';

SELECT 
    DATEDIFF(year, @start_dt, @end_dt) diff_in_year, 
    DATEDIFF(quarter, @start_dt, @end_dt) diff_in_quarter, 
    DATEDIFF(month, @start_dt, @end_dt) diff_in_month, 
    DATEDIFF(dayofyear, @start_dt, @end_dt) diff_in_dayofyear, 
    DATEDIFF(day, @start_dt, @end_dt) diff_in_day, 
    DATEDIFF(week, @start_dt, @end_dt) diff_in_week, 
    DATEDIFF(hour, @start_dt, @end_dt) diff_in_hour, 
    DATEDIFF(minute, @start_dt, @end_dt) diff_in_minute, 
    DATEDIFF(second, @start_dt, @end_dt) diff_in_second, 
    DATEDIFF(millisecond, @start_dt, @end_dt) diff_in_millisecond;

Result:

Use the DATEDIFF() function with table columns

The following example uses the DATEDIFF() function to compare the requested shipping date with the scheduled ship date and return the order result on time or late:

SELECT
    order_id, 
    required_date, 
    shipped_date,
    CASE
        WHEN DATEDIFF(day, required_date, shipped_date) 

Result:

Select constants from date1 and date2 with negative values

Use the SQL Server date comparison function and get the negative difference between two date values, in days.

For example:

SELECT DATEDIFF(day, '2021/2/1', '2010/12/12');

Result:

-3704

Select constants from date1 and date2 according to the hour

Use the DATEDIFF() function and get the difference between two date values, including the time, in hours.

For example:

SELECT DATEDIFF(hour, '2019/2/1 09:55', '2020/12/12 07:45');

Result:

16318

In short, things to keep in mind when using DATEDIFF function in SQL Server

• The DATEDIFF function handles date & time values ​​as arguments.
• The compiler generates an error if a non-date format variable is attempted for function arguments.
• This function is useful when you need to find the difference between different date parts of a timestamp.

Above are the basics of the DATEDIFF() function in SQL Server, helping you calculate the difference between two date values. Hope the article is useful to you.

Previous article: CURRENT_TIMESTAMP function in SQL Server

Next article: DATEADD function in SQL Server