Shulou(Shulou.com)06/01 Report--

This article mainly introduces how to use the datediff function in SQL, which is very detailed and has certain reference value. Friends who are interested must finish it!

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

The syntax of the DATEDIFF () function is as follows:

DATEDIFF (datepart, startdate, enddate)

Where datepart is part of the date you want to compare. Startdate is the first date and enddate is the end date.

It works 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

Results:

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

Example 2

Here is another example where I declare two variables and assign them two different dates (I use DATEADD () to add the first date for one year). Then use DATEDIFF () to return each dateparts for that date:

DECLARE @ date1 datetime2 = '2000-01-01 0000-000000000000000000000000000There are 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

Results:

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

Example 3

As mentioned earlier, you can also return to the time part between dates. Here is an example of returning the number of hours, minutes, and seconds between date / time values:

DECLARE @ date1 datetime2 = '2000-01-000000000000000000000000000000000000000000011; SELECT DATEDIFF (hour, @ date1, @ date2) AS Hours, DATEDIFF (minute, @ date1, @ date2) AS Minutes, DATEDIFF (second, @ date1, @ date2) AS Seconds

Results:

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

Example 4

Here is an example of getting milliseconds, microseconds, and nanoseconds between two date / time values:

DECLARE @ date1 datetime2 = '2000-01-000000000000000000000000000000000000000000011; SELECT DATEDIFF (millisecond, @ date1, @ date2) AS Milliseconds, DATEDIFF (microsecond, @ date1, @ date2) AS Microseconds, DATEDIFF (nanosecond, @ date1, @ date2) AS Nanoseconds

Results:

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

Example 5-Error error!

If you try to do something extreme, such as returning to the number of nanoseconds 100 years later, you will get an error. This is because DATEDIFF () returns an int value, and the number of nanoseconds within 100 years is more than the number of nanoseconds that the int data type can handle.

What will happen if you try to do this?

DECLARE @ date1 datetime2 = '2000-01-0000000000000000000000000000000000000000011; SELECT DATEDIFF (millisecond, @ date1, @ date2) AS Milliseconds, DATEDIFF (microsecond, @ date1, @ date2) AS Microseconds, DATEDIFF (nanosecond, @ date1, @ date2) AS Nanoseconds

Results:

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, you can use the DATEDIFF_BIG () function. This function returns a signed bigint data type, which allows you to return a larger value than DATEDIFF ().

This is all about how to use the datediff function in SQL. Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.