Как найти разницу дат в sql

T-SQL предоставляет ряд функций для работы с датами и временем:

GETDATE : возвращает текущую локальную дату и время на основе системных часов в виде объекта datetime

GETUTCDATE : возвращает текущую локальную дату и время по гринвичу (UTC/GMT) в виде объекта datetime

SYSDATETIME : возвращает текущую локальную дату и время на основе системных часов, но отличие от GETDATE состоит в том, что дата и время возвращаются в виде объекта datetime2

SYSUTCDATETIME : возвращает текущую локальную дату и время по гринвичу (UTC/GMT) в виде объекта datetime2

SYSDATETIMEOFFSET : возвращает объект datetimeoffset(7), который содержит дату и время относительно GMT

DAY : возвращает день даты, который передается в качестве параметра

MONTH : возвращает месяц даты

YEAR : возвращает год из даты

DATENAME : возвращает часть даты в виде строки. Параметр выбора части даты передается в качестве первого параметра, а сама дата передается в качестве второго параметра:

Для определения части даты можно использовать следующие параметры (в скобках указаны их сокращенные версии):

year (yy, yyyy) : год

quarter (qq, q) : квартал

month (mm, m) : месяц

dayofyear (dy, y) : день года

day (dd, d) : день месяца

week (wk, ww) : неделя

weekday (dw) : день недели

minute (mi, n) : минута

second (ss, s) : секунда

millisecond (ms) : миллисекунда

microsecond (mcs) : микросекунда

nanosecond (ns) : наносекунда

tzoffset (tz) : смешение в минутах относительно гринвича (для объекта datetimeoffset)

DATEPART : возвращает часть даты в виде числа. Параметр выбора части даты передается в качестве первого параметра (используются те же параметры, что и для DATENAME), а сама дата передается в качестве второго параметра:

DATEADD : возвращает дату, которая является результатом сложения числа к определенному компоненту даты. Первый параметр представляет компонент даты, описанный выше для функции DATENAME. Второй параметр — добавляемое количество. Третий параметр — сама дата, к которой надо сделать прибавление:

Если добавляемое количество представляет отрицательное число, то фактически происходит уменьшение даты.

DATEDIFF : возвращает разницу между двумя датами. Первый параметр — компонент даты, который указывает, в каких единицах стоит измерять разницу. Второй и третий параметры — сравниваемые даты:

TODATETIMEOFFSET : возвращает значение datetimeoffset, которое является результатом сложения временного смещения с объектом datetime2

SWITCHOFFSET : возвращает значение datetimeoffset, которое является результатом сложения временного смещения с другим объектом datetimeoffset

EOMONTH : возвращает дату последнего дня для месяца, который используется в переданной в качестве параметра дате.

В качестве необязательного второго параметра можно передавать количество месяцев, которые необходимо прибавить к дате. Тогда последний день месяца будет вычисляться для новой даты.

DATEFROMPARTS : по году, месяцу и дню создает дату

ISDATE : проверяет, является ли выражение датой. Если является, то возвращает 1, иначе возвращает 0.

В качестве примера использования функций можно привести создание таблицы заказов, которая содержит дату заказа:

Выражение DEFAULT GETDATE() указывает, что если при добавлении данных не передается дата, то она автоматически вычисляется с помощью функции GETDATE().

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

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

J. Specifying columns for startdate and enddate

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

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

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

L. Specifying constants for startdate and enddate

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

M. Specifying ranking functions for startdate

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

N. Specifying an aggregate window function for startdate

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

SQL DATEDIFF

Summary: in this tutorial, you will learn how to use the SQL DATEDIFF() function to calculate the difference between two dates.

Syntax

To calculate the difference between two dates, you use the DATEDIFF() function. The following illustrates the syntax of the DATEDIFF() function in SQL Server:

Arguments

datepart

The datepart is a part of the date in which you want the function to return. The following table illustrates the valid parts of date in SQL Server:

Valid Date Part	Abbreviations
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
weekday	dw, w
hour	hh
minute	mi, n
second	ss, s
millisecond	ms
microsecond	mcs
nanosecond	ns

startdate, enddate

The startdate and enddate are date literals or expressions from which you want to find the difference.

Return

The DATEDIFF() function returns an integer value with the unit specified by the datepart argument.

Examples

The following example returns the number of year between two dates:

Here is the result:

To get the number of month or day, you change the first argument to month or day as shown below:

The following shows the result:

Notice that the DATEDIFF() function takes the leap year into account. As shown clearly in the result, because 2016 is the leap year, the difference in days between two dates is 2×365 + 366 = 1096.

The following example illustrates how to use the DATEDIFF() function to calculate the difference in hours between two DATETIME values:

Consider the following example:

It also returns two because the DATEDIFF() function returns an integer only. In this case, it truncated the minute part and only consider the hour part.

The following example shows how to use the DATEDIFF() function to calculate the year of services of employees up to January 1st, 2018:

DATEDIFF in MySQL

Unlike SQL Server, MySQL has a slightly different DATEDIFF() function syntax:

MySQL only returns the difference between two dates in days. It ignores all the time part of the date in the calculation. See the following example:

The result is nine days:

In this tutorial, you have learned how to use the SQL DATEDIFF() function to calculate the difference between two dates.

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)

Как найти разницу дат в sql