How to use the datediff function in SQL? (detailed code explanation)

In SQL Server, you can use the T-SQL DATEDIFF() function to return the difference between two dates. It works with any expression that can be parsed into a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value. Therefore, you can also get the difference between the two times.

This article provides examples of usage of the DATEDIFF() function in SQL Server.

DATEDIFF() function syntax is as follows:

DATEDIFF ( datepart , startdate , enddate )

where datepart is the part of the date you want to compare. startdate is the first date and enddate is the end date.

The way it works is by subtracting startdate from enddate.

Example 1

Here is a basic example where we can calculate the number of days between two dates:

SELECT DATEDIFF(day, '2001-01-01', '2002-01-01') AS Result;

Result:

+----------+
| Result   |
|----------|
| 365      |
+----------+

Example 2

Here is another example where I declared two variables and assigned them two different dates (I used DATEADD() to Add 1 year to the first date). Then use DATEDIFF() to return the individual dateparts for that date:

DECLARE @date1 datetime2 = '2000-01-01 00:00:00.0000000';
DECLARE @date2 datetime2 = DATEADD(year, 1, @date1);
SELECT 
    DATEDIFF( year, @date1, @date2 ) AS Years,
    DATEDIFF( quarter, @date1, @date2 ) AS Quarters,
    DATEDIFF( month, @date1, @date2 ) AS Months,
    DATEDIFF( week, @date1, @date2 ) AS Weeks,
    DATEDIFF( dayofyear, @date1, @date2 ) AS DayOfYear,
    DATEDIFF( day, @date1, @date2 ) AS Days;

Result:

+---------+------------+----------+---------+-------------+--------+
| Years   | Quarters   | Months   | Weeks   | DayOfYear   | Days   |
|---------+------------+----------+---------+-------------+--------|
| 1       | 4          | 12       | 53      | 366         | 366    |
+---------+------------+----------+---------+-------------+--------+

Example 3

As mentioned before, You can also return the time portion between dates. Here is an example of returning hours, minutes and seconds between date/time values:

DECLARE @date1 datetime2 = '2000-01-01 00:00:00.0000000';
DECLARE @date2 datetime2 = DATEADD(hour, 1, @date1);
SELECT 
    DATEDIFF( hour, @date1, @date2 ) AS Hours,
    DATEDIFF( minute, @date1, @date2 ) AS Minutes,
    DATEDIFF( second, @date1, @date2 ) AS Seconds;

Result:

+---------+-----------+-----------+
| Hours   | Minutes   | Seconds   |
|---------+-----------+-----------|
| 1       | 60        | 3600      |
+---------+-----------+-----------+

Example 4

The following is an example of getting the number of milliseconds, microseconds and nanoseconds between two date/time values:

DECLARE @date1 datetime2 = '2000-01-01 00:00:00.0000000';
DECLARE @date2 datetime2 = DATEADD(millisecond, 1, @date1);
SELECT    
    DATEDIFF( millisecond, @date1, @date2 ) AS Milliseconds,
    DATEDIFF( microsecond, @date1, @date2 ) AS Microseconds,
    DATEDIFF( nanosecond, @date1, @date2 ) AS Nanoseconds;

Result:

+----------------+----------------+---------------+
| Milliseconds   | Microseconds   | Nanoseconds   |
|----------------+----------------+---------------|
| 1              | 1000           | 1000000       |
+----------------+----------------+---------------+

Example 5 - Error!

If you try to do something extreme, like return the number of nanoseconds after 100 years, you'll get an error. This is because DATEDIFF() returns an int value, and there are more nanoseconds in 100 years than the int data type can handle.

What happens if you try this?

DECLARE @date1 datetime2 = '2000-01-01 00:00:00.0000000';
DECLARE @date2 datetime2 = DATEADD(year, 100, @date1);
SELECT    
    DATEDIFF( millisecond, @date1, @date2 ) AS Milliseconds,
    DATEDIFF( microsecond, @date1, @date2 ) AS Microseconds,
    DATEDIFF( nanosecond, @date1, @date2 ) AS Nanoseconds;

Result:

The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

Of course if you really have to find out how many nanoseconds there are in 100 years , then you can use the DATEDIFF_BIG() function. This function returns a signed bigint data type, which allows you to return larger values ​​than DATEDIFF().

Related recommendations: "MySQL Tutorial"

The above is the detailed content of How to use the datediff function in SQL? (detailed code explanation). For more information, please follow other related articles on the PHP Chinese website!