Как найти разницу во времени в sql

DATEDIFF() Examples in SQL Server

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

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

Syntax

First, here’s the syntax:

Where datepart is the part of the date that you want compared. startdate is the first date and enddate is the end date.

The way it works is that it returns the count (as a signed integer value) of the specified datepart boundaries crossed between the specified startdate and enddate.

Example 1

Here’s a basic example where we find out the number of days between two dates:

Example 2

Here’s another example where I declare two variables and assign two different dates to them (I use DATEADD() to add 1 year to the first date). I then use DATEDIFF() to return various dateparts for that date:

Example 3

As mentioned, you can also return the time parts between the dates. Here’s an example of returning the number of hours, minutes, and seconds between the date/time values:

Example 4

And here’s an example of getting the number of milliseconds, microseconds, and nanoseconds between two date/time values:

Example 5 – Error!

If you try to do something extreme, like, return the number of nanoseconds in 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.

So here’s what happens if you try to do that:

Fortunately, if you really must find out how many nanoseconds are in 100 years, you can use the DATEDIFF_BIG() function instead. This function returns a signed bigint data type, which allows you to return much larger values than DATEDIFF() can.

Example 6 – Getting Weird Results?

The results you get from DATEDIFF() can sometimes look completely wrong if you don’t know how the function actually works.

If you don’t know how DATEDIFF() actually works, this result could look so wrong, that you’d be forgiven for assuming that it’s a bug. But it’s not a bug.

Check out DATEDIFF() Returns Wrong Results in SQL Server? Read This. to see this example and other cases where the results can look completely wrong, but be perfectly correct (and for an explanation on why they look the way they do).

One of the examples on that page is probably worth mentioning again here. DATEDIFF() actually ignores your SET DATEFIRST value. This can result in unexpected results, especially if you’re in a culture that doesn’t use Sunday as the first day of the week. Check out this Workaround for DATEDIFF() Ignoring SET DATEFIRST in SQL Server if you think this might affect you.

SQL Server функция DATEDIFF

В SQL Server (Transact-SQL) функция DATEDIFF возвращает разность между двумя значениями даты в зависимости от указанного интервала.

Синтаксис

Синтаксис функции DATEDIFF в SQL Server (Transact-SQL):

Параметры или аргументы

interval — интервал времени для вычисления разницы между date1 и date2 . Это может быть одно из следующих значений:

Значение (любое из)	Пояснение
year, yyyy, yy	Год интервал
quarter, qq, q	Квартал интервал
month, mm, m	Месяца интервал
dayofyear	День года интервал
day, dy, y	День интервал
week, ww, wk	Неделя интервал
weekday, dw, w	День недели интервал
hour, hh	Час интервал
minute, mi, n	Минуты интервал
second, ss, s	Секунды интервал
millisecond, ms	Миллисекунды интервал
microsecond, mcs	Микросекунды интервал
nanosecond, ns	Наносекунды интервал

date1 , date2 — две даты для расчета разницы между ними.

Применение

Функция DATEDIFF может использоваться в следующих версиях SQL Server (Transact-SQL):
SQL Server vNext, SQL Server 2016, SQL Server 2015, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005

Пример

Рассмотрим некоторые примеры SQL Server функции DATEDIFF, чтобы понять, как использовать функцию DATEDIFF в SQL Server (Transact-SQL). Например:

Difference of two date time in sql server

Is there any way to take the difference between two datetime in sql server?

For example, my dates are

2010-01-22 15:29:55.090
2010-01-22 15:30:09.153

So, the result should be 14.063 seconds .

22 Answers 22

Just a caveat to add about DateDiff, it counts the number of times you pass the boundary you specify as your units, so is subject to problems if you are looking for a precise timespan. e.g.

gives an answer of 1, because it crossed the boundary from January to February, so even though the span is 2 days, datediff would return a value of 1 — it crossed 1 date boundary.

Gives a value of 1, again, it passed the minute boundary once, so even though it is approx 14 seconds, it would be returned as a single minute when using Minutes as the units.

Substitute «MyUnits» based on DATEDIFF on MSDN

Replace day with other units you want to get the difference in, like second , minute etc.

I can mention four important functions of MS SQL Server that can be very useful:

1) The function DATEDIFF() is responsible to calculate differences between two dates, the result could be «year quarter month dayofyear day week hour minute second millisecond microsecond nanosecond«, specified on the first parameter (datepart):

2) You can use the function GETDATE() to get the actual time and calculate differences of some date and actual date:

3) Another important function is DATEADD(), used to convert some value in datetime using the same datepart of the datediff, that you can add (with positive values) or substract (with negative values) to one base date:

4) The function CONVERT() was made to format the date like you need, it is not parametric function, but you can use part of the result to format the result like you need:

DATETIME cold be calculated in seconds and one interesting result mixing these four function is to show a formated difference um hours, minutes and seconds (hh:mm:ss) between two dates:

. the result is 00:10:38 (638s = 600s + 38s = 10 minutes and 38 seconds)

Name already in use

sql-docs / docs / t-sql / functions / datediff-transact-sql.md

Go to file T
Go to line L
Copy path
Copy permalink

Open with Desktop
View raw
Copy raw contents Copy raw contents

Copy raw contents

This function returns the count (as a signed integer value) of the specified datepart boundaries crossed between the specified startdate and enddate.

See DATEDIFF_BIG (Transact-SQL) for a function that handles larger differences between the startdate and enddate values. See Date and Time Data Types and Functions (Transact-SQL) for an overview of all [!INCLUDEtsql] date and time data types and functions.

. image type=»icon» source=»../../includes/media/topic-link-icon.svg» border=»false». Transact-SQL syntax conventions

datepart
The units in which DATEDIFF reports the difference between the startdate and enddate. Commonly used datepart units include month or second .

The datepart value cannot be specified in a variable, nor as a quoted string like ‘month’ .

The following table lists all the valid datepart values. DATEDIFF accepts either the full name of the datepart, or any listed abbreviation of the full name.

datepart name	datepart abbreviation
year	y, yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy
day	dd, d
week	wk, ww
hour	hh
minute	mi, n
second	ss, s
millisecond	ms
microsecond	mcs
nanosecond	ns

[!NOTE] Each specific datepart name and abbreviations for that datepart name will return the same value.

startdate
An expression that can resolve to one of the following values:

date
datetime
datetimeoffset
datetime2
smalldatetime
time

Use four-digit years to avoid ambiguity. See Configure the two digit year cutoff Server Configuration Option for information about two-digit year values.

int

The int difference between the startdate and enddate, expressed in the boundary set by datepart.

For example, SELECT DATEDIFF(day, ‘2036-03-01’, ‘2036-02-28’); returns -2, hinting that 2036 must be a leap year. This case means that if we start at startdate ‘2036-03-01’, and then count -2 days, we reach the enddate of ‘2036-02-28’.

For a return value out of range for int (-2,147,483,648 to +2,147,483,647), DATEDIFF returns an error. For millisecond, the maximum difference between startdate and enddate is 24 days, 20 hours, 31 minutes and 23.647 seconds. For second, the maximum difference is 68 years, 19 days, 3 hours, 14 minutes and 7 seconds.

If startdate and enddate are both assigned only a time value, and the datepart is not a time datepart, DATEDIFF returns 0.

DATEDIFF uses the time zone offset component of startdate or enddate to calculate the return value.

Because smalldatetime is accurate only to the minute, seconds and milliseconds are always set to 0 in the return value when startdate or enddate have a smalldatetime value.

If only a time value is assigned to a date data type variable, DATEDIFF sets the value of the missing date part to the default value: 1900-01-01 . If only a date value is assigned to a variable of a time or date data type, DATEDIFF sets the value of the missing time part to the default value: 00:00:00 . If either startdate or enddate have only a time part and the other only a date part, DATEDIFF sets the missing time and date parts to the default values.

If startdate and enddate have different date data types, and one has more time parts or fractional seconds precision than the other, DATEDIFF sets the missing parts of the other to 0.

The following statements have the same startdate and the same enddate values. Those dates are adjacent and they differ in time by a hundred nanoseconds (.0000001 second). The difference between the startdate and enddate in each statement crosses one calendar or time boundary of its datepart. Each statement returns 1.

If startdate and enddate have different year values, but they have the same calendar week values, DATEDIFF will return 0 for datepart week.

Use DATEDIFF in the SELECT <list> , WHERE , HAVING , GROUP BY and ORDER BY clauses.

DATEDIFF implicitly casts string literals as a datetime2 type. This means that DATEDIFF does not support the format YDM when the date is passed as a string. You must explicitly cast the string to a datetime or smalldatetime type to use the YDM format.

Specifying SET DATEFIRST has no effect on DATEDIFF . DATEDIFF always uses Sunday as the first day of the week to ensure the function operates in a deterministic way.

DATEDIFF may overflow with a precision of minute or higher if the difference between enddate and startdate returns a value that is out of range for int.

These examples use different types of expressions as arguments for the startdate and enddate parameters.

A. Specifying columns for startdate and enddate

This example calculates the number of day boundaries crossed between dates in two columns in a table.

B. Specifying user-defined variables for startdate and enddate

In this example, user-defined variables serve as arguments for startdate and enddate.

C. Specifying scalar system functions for startdate and enddate

This example uses scalar system functions as arguments for startdate and enddate.

D. Specifying scalar subqueries and scalar functions for startdate and enddate

This example uses scalar subqueries and scalar functions as arguments for startdate and enddate.

E. Specifying constants for startdate and enddate

This example uses character constants as arguments for startdate and enddate.

F. Specifying numeric expressions and scalar system functions for enddate

This example uses a numeric expression, (GETDATE() + 1) , and scalar system functions GETDATE and SYSDATETIME , as arguments for enddate.

G. Specifying ranking functions for startdate

This example uses a ranking function as an argument for startdate.

H. Specifying an aggregate window function for startdate

This example uses an aggregate window function as an argument for startdate.

I. Finding difference between startdate and enddate as date parts strings