Как в sql посчитать количество дней между датами

DATEDIFF (Transact-SQL)

Эта функция возвращает количество пересеченных границ (целое число со знаком), указанных в аргументе datepart, за период времени, указанный в аргументах startdate и enddate.

Сведения о функции, которая обрабатывает значительные отличия значений аргументов startdate и enddate, см. в статье DATEDIFF_BIG (Transact-SQL). Обзор всех типов данных и функций даты и времени в языке Transact-SQL см. в статье Типы данных и функции даты и времени (Transact-SQL).

Синтаксис

DATEDIFF ( datepart , startdate , enddate ) 

Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.

Аргументы

datepart
Единицы, в которых функция DATEDIFF сообщает о разнице между startdate и enddate. К часто используемым единицам datepart относятся month или second .

Значение datepart не может быть указано в переменной или в виде строки в кавычках, например ‘month’ .

В приведенной ниже таблице перечислены все допустимые значения datepart. Функция DATEDIFF принимает либо полное имя datepart, либо любое приведенное сокращение полного имени.

Имя datepart	Сокращение datepart
year	y, yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy
day	dd, d
week	wk, ww
weekday	dw, w
hour	hh
minute	mi, n
second	ss, s
millisecond	ms
microsecond	mcs
nanosecond	ns

Каждое конкретное имя аргумента datepart и сокращение этого имени datepart будут возвращать одно и то же значение.

startdate
Выражение, которое может быть разрешено в одно из следующих значений.

Во избежание неоднозначности используйте четырехзначную запись года. Сведения о двузначном обозначении года см. в статье Настройка параметра конфигурации сервера two digit year cutoff.

enddate
См. описание аргумента startdate.

Тип возвращаемых данных

int

Возвращаемое значение

Значение типа int, представляющее разницу между аргументами startdate и enddate в границах, определяемых аргументом datepart.

Например, SELECT DATEDIFF(day, ‘2036-03-01’, ‘2036-02-28’); возвращает значение -2, указывая на то, что 2036 – високосный год. Это означает, что, если начать со startdDate «2036-03-01» и отсчитать -2 дня, будет достигнуто значение enddate «2036-02-28».

В качестве возвращаемого значения вне диапазона для int (от –2 147 483 648 до 2 147 483 647) DATEDIFF возвращает сообщение об ошибке. Для миллисекунды максимальная разница между startdate и enddate составляет 24 дня 20 часов 31 минута и 23 647 секунд. Для секунды максимальная разница составляет 68 лет, 19 дней, 3 часа, 14 минут и 7 секунд.

Если обоим аргументам, startdate и enddate, присвоено только значение времени, а аргумент datepart не содержит значения времени datepart, то DATEDIFF возвращает значение 0.

При вычислении возвращаемого значения DATEDIFF использует компонент смещения часовых поясов для аргументов startdate или enddate.

Так как значение типа smalldatetime имеет точность до минуты, то при использовании в аргументах startdate и enddate значений типа smalldatetime секунды и миллисекунды у возвращаемых значений всегда равны 0.

Если переменной типа данных date присвоено только значение времени, в качестве недостающей части даты DATEDIFF задает значение по умолчанию: 1900-01-01 . Если переменной типа данных time или date присвоено только значение даты, в качестве недостающей части времени DATEDIFF задает значение по умолчанию: 00:00:00 . Если в одном из аргументов startdate или enddate указано только время, а в другом только дата, в качестве недостающей информации DATEDIFF задает значения по умолчанию.

Если аргументы startdate и enddate имеют разные типы данных даты, но при этом один из них имеет больше частей времени или обладает более высокой точностью, DATEDIFF присваивает значения 0 недостающим частям другого аргумента.

Границы, задаваемые аргументом datepart

Приведенные ниже инструкции имеют одинаковые значения аргументов startdate и enddate. Указанные даты являются соседними, а временная разница между ними составляет 100 наносекунд (0,0000001 секунды). Разница между аргументами startdate и enddate в каждой инструкции пересекает одну календарную или временную границу аргумента datepart. Каждое выражение возвращает значение 1.

SELECT DATEDIFF(year, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); SELECT DATEDIFF(quarter, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); SELECT DATEDIFF(month, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); SELECT DATEDIFF(dayofyear, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); SELECT DATEDIFF(day, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); SELECT DATEDIFF(week, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); SELECT DATEDIFF(weekday, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); SELECT DATEDIFF(hour, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); SELECT DATEDIFF(minute, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); SELECT DATEDIFF(second, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); SELECT DATEDIFF(millisecond, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); SELECT DATEDIFF(microsecond, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000'); 

Если аргументы startdate и enddate имеют разные значения года, но одинаковые значения календарной недели, DATEDIFF вернет 0 для части week аргумента datepart.

Замечания

Используйте DATEDIFF в предложениях SELECT , WHERE , HAVING , GROUP BY и ORDER BY .

Функция DATEDIFF неявно приводит строковые литералы к типу datetime2. Это означает, что DATEDIFF не поддерживает формат ГЧМ (год, число, месяц) при передаче даты в виде строки. Для использования формата ГЧМ (год, число, месяц) необходимо явно привести строку к типу datetime или smalldatetime.

Указание SET DATEFIRST не влияет на DATEDIFF . DATEDIFF всегда считает воскресенье первым днем недели, чтобы обеспечить детерминизм работы функции.

DATEDIFF может переполняться с точностью до минуты или более низкой точностью, если разница между enddate и startdate представляет собой значение, выходящее за пределы диапазона, допустимого для int.

Примеры

В этих примерах выражения различного типа используются в качестве аргументов для параметров startdate и enddate.

А. Указание столбцов в качестве начальной и конечной даты

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

CREATE TABLE dbo.Duration (startDate datetime2, endDate datetime2); INSERT INTO dbo.Duration(startDate, endDate) VALUES ('2007-05-06 12:10:09', '2007-05-07 12:10:09'); SELECT DATEDIFF(day, startDate, endDate) AS 'Duration' FROM dbo.Duration; -- Returns: 1 

B. Указание определенных пользователем переменных в качестве начальной и конечной даты

В этом примере в качестве аргументов startdate и enddate выступают определенные пользователем переменные.

DECLARE @startdate DATETIME2 = '2007-05-05 12:10:09.3312722'; DECLARE @enddate DATETIME2 = '2007-05-04 12:10:09.3312722'; SELECT DATEDIFF(day, @startdate, @enddate); 

C. Указание скалярных системных функций в качестве начальной и конечной даты

В этом примере в качестве аргументов startdate и enddate выступают скалярные системные функции.

SELECT DATEDIFF(millisecond, GETDATE(), SYSDATETIME()); 

D. Указание скалярных вложенных запросов и скалярных функций в качестве начальной и конечной даты

В этом примере в качестве аргументов startdate и enddate выступают скалярные вложенные запросы.

USE AdventureWorks2022; GO SELECT DATEDIFF(day, (SELECT MIN(OrderDate) FROM Sales.SalesOrderHeader), (SELECT MAX(OrderDate) FROM Sales.SalesOrderHeader)); 

Д. Указание констант в качестве начальной и конечной даты

В этом примере в качестве аргументов startdate и enddate используются символьные константы.

SELECT DATEDIFF(day, '2007-05-07 09:53:01.0376635', '2007-05-08 09:53:01.0376635'); 

F. Указание числовых выражений и скалярных системных функций в качестве конечной даты

В этом примере в качестве аргументов для enddate используются числовое выражение (GETDATE() + 1) и скалярные системные функции GETDATE и SYSDATETIME .

USE AdventureWorks2022; GO SELECT DATEDIFF(day, '2007-05-07 09:53:01.0376635', GETDATE() + 1) AS NumberOfDays FROM Sales.SalesOrderHeader; GO USE AdventureWorks2022; GO SELECT DATEDIFF( day, '2007-05-07 09:53:01.0376635', DATEADD(day, 1, SYSDATETIME()) ) AS NumberOfDays FROM Sales.SalesOrderHeader; GO 

G. Указание ранжирующих функций в качестве начальной даты

В этом примере в качестве аргумента startdate. используется ранжирующая функция.

USE AdventureWorks2022; GO SELECT p.FirstName, p.LastName ,DATEDIFF(day, ROW_NUMBER() OVER (ORDER BY a.PostalCode), SYSDATETIME()) AS 'Row Number' FROM Sales.SalesPerson s INNER JOIN Person.Person p ON s.BusinessEntityID = p.BusinessEntityID INNER JOIN Person.Address a ON a.AddressID = p.BusinessEntityID WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0; 

H. Указание агрегатной оконной функции в качестве начальной даты

В этом примере в качестве аргумента startdate используется агрегатная оконная функция.

USE AdventureWorks2022; GO SELECT soh.SalesOrderID, sod.ProductID, sod.OrderQty, soh.OrderDate, DATEDIFF(day, MIN(soh.OrderDate) OVER(PARTITION BY soh.SalesOrderID), SYSDATETIME()) AS 'Total' FROM Sales.SalesOrderDetail sod INNER JOIN Sales.SalesOrderHeader soh ON sod.SalesOrderID = soh.SalesOrderID WHERE soh.SalesOrderID IN(43659, 58918); GO 

I. Определение разницы между startdate и enddate в виде строковых компонентов даты

-- DOES NOT ACCOUNT FOR LEAP YEARS DECLARE @date1 DATETIME, @date2 DATETIME, @result VARCHAR(100); DECLARE @years INT, @months INT, @days INT, @hours INT, @minutes INT, @seconds INT, @milliseconds INT; SET @date1 = '1900-01-01 00:00:00.000' SET @date2 = '2018-12-12 07:08:01.123' SELECT @years = DATEDIFF(yy, @date1, @date2) IF DATEADD(yy, -@years, @date2) < @date1 SELECT @years = @years-1 SET @date2 = DATEADD(yy, -@years, @date2) SELECT @months = DATEDIFF(mm, @date1, @date2) IF DATEADD(mm, -@months, @date2) < @date1 SELECT @months=@months-1 SET @date2= DATEADD(mm, -@months, @date2) SELECT @days=DATEDIFF(dd, @date1, @date2) IF DATEADD(dd, -@days, @date2) < @date1 SELECT @days=@days-1 SET @date2= DATEADD(dd, -@days, @date2) SELECT @hours=DATEDIFF(hh, @date1, @date2) IF DATEADD(hh, -@hours, @date2) < @date1 SELECT @hours=@hours-1 SET @date2= DATEADD(hh, -@hours, @date2) SELECT @minutes=DATEDIFF(mi, @date1, @date2) IF DATEADD(mi, -@minutes, @date2) < @date1 SELECT @minutes=@minutes-1 SET @date2= DATEADD(mi, -@minutes, @date2) SELECT @seconds=DATEDIFF(s, @date1, @date2) IF DATEADD(s, -@seconds, @date2) < @date1 SELECT @seconds=@seconds-1 SET @date2= DATEADD(s, -@seconds, @date2) SELECT @milliseconds=DATEDIFF(ms, @date1, @date2) SELECT @result= ISNULL(CAST(NULLIF(@years,0) AS VARCHAR(10)) + ' years,','') + ISNULL(' ' + CAST(NULLIF(@months,0) AS VARCHAR(10)) + ' months,','') + ISNULL(' ' + CAST(NULLIF(@days,0) AS VARCHAR(10)) + ' days,','') + ISNULL(' ' + CAST(NULLIF(@hours,0) AS VARCHAR(10)) + ' hours,','') + ISNULL(' ' + CAST(@minutes AS VARCHAR(10)) + ' minutes and','') + ISNULL(' ' + CAST(@seconds AS VARCHAR(10)) + CASE WHEN @milliseconds >0 THEN '.' + CAST(@milliseconds AS VARCHAR(10)) ELSE '' END + ' seconds','') SELECT @result 

118 years, 11 months, 11 days, 7 hours, 8 minutes and 1.123 seconds 

Примеры: Azure Synapse Analytics и система платформы аналитики (PDW)

В этих примерах выражения различного типа используются в качестве аргументов для параметров startdate и enddate.

J. Указание столбцов в качестве начальной и конечной даты

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

CREATE TABLE dbo.Duration (startDate datetime2, endDate datetime2); INSERT INTO dbo.Duration (startDate, endDate) VALUES ('2007-05-06 12:10:09', '2007-05-07 12:10:09'); SELECT TOP(1) DATEDIFF(day, startDate, endDate) AS Duration FROM dbo.Duration; -- Returns: 1 

K. Указание скалярных вложенных запросов и скалярных функций в качестве начальной и конечной даты

В этом примере в качестве аргументов startdate и enddate выступают скалярные вложенные запросы.

-- Uses AdventureWorks SELECT TOP(1) DATEDIFF(day, (SELECT MIN(HireDate) FROM dbo.DimEmployee), (SELECT MAX(HireDate) FROM dbo.DimEmployee)) FROM dbo.DimEmployee; 

L. Указание констант в качестве начальной и конечной даты

В этом примере в качестве аргументов startdate и enddate используются символьные константы.

-- Uses AdventureWorks SELECT TOP(1) DATEDIFF(day, '2007-05-07 09:53:01.0376635', '2007-05-08 09:53:01.0376635') FROM DimCustomer; 

M. Указание ранжирующих функций в качестве начальной даты

В этом примере в качестве аргумента startdate. используется ранжирующая функция.

-- Uses AdventureWorks SELECT FirstName, LastName, DATEDIFF(day, ROW_NUMBER() OVER (ORDER BY DepartmentName), SYSDATETIME()) AS RowNumber FROM dbo.DimEmployee; 

О. Указание агрегатной оконной функции в качестве начальной даты

В этом примере в качестве аргумента startdate используется агрегатная оконная функция.

-- Uses AdventureWorks SELECT FirstName, LastName, DepartmentName, DATEDIFF(year, MAX(HireDate) OVER (PARTITION BY DepartmentName), SYSDATETIME()) AS SomeValue FROM dbo.DimEmployee 

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

Для нахождения разницы между двумя значениями в SQL можно использовать различные функции и операторы, в зависимости от конкретной задачи.

Например, если необходимо найти разницу между двумя датами, можно использовать функцию DATEDIFF() . Эта функция возвращает количество временных единиц (например, дней, месяцев, лет) между двумя датами. Синтаксис функции выглядит следующим образом:

DATEDIFF(interval, startdate, enddate) 

где interval — это временная единица, которую необходимо использовать для измерения разницы, startdate — это начальная дата, а enddate — это конечная дата.

Например, чтобы найти разницу между двумя датами в днях, можно использовать следующий запрос:

SELECT DATEDIFF(day, '2022-01-01', '2022-01-15') AS days_diff; 

Этот запрос вернет значение 14, что означает, что между 1 января 2022 года и 15 января 2022 года прошло 14 дней.

Если же необходимо найти разницу между двумя числовыми значениями, можно использовать оператор вычитания — . Например, чтобы найти разницу между двумя числами в таблице orders, можно использовать следующий запрос:

SELECT price_paid - price_listed AS price_diff FROM orders; 

Этот запрос вернет столбец price_diff, содержащий разницу между значениями в столбцах price_paid и price_listed.

Посчитать дни между датами с условием

Есть две переменные DATE_IN и END_DATE .
Если DATE_IN и END_DATE равны, то считаем как 1 день, а если DATE_IN и END_DATE не равны, то считаем, как обычно, — количество дней между ними. Как правильно написать select или функцию под эти условия?

Отслеживать
110k 23 23 золотых знака 113 113 серебряных знаков 377 377 бронзовых знаков
задан 18 июл 2019 в 14:00
49 1 1 серебряный знак 7 7 бронзовых знаков
SELECT CASE WHEN END_DATE = DATE_IN THEN 1 ELSE END_DATE — DATE_IN END AS DATE_DIFF FROM . ?
18 июл 2019 в 14:03

Если я не ошибаюсь ,то в else так вставить не получиться выплюнет ошибку. Либо я лох������ и мне пора в отпуск. Приеду дом крутану этот запрос

18 июл 2019 в 14:06
Практика — критерий истины 😉
18 июл 2019 в 14:08
Спасибо ,а то я как то походу слишком глубоко капнул ,приеду попробую отпишу
18 июл 2019 в 14:17

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

19 июл 2019 в 10:36

1 ответ 1

Сортировка: Сброс на вариант по умолчанию

with function getDateDiff (start_date date, end_date date) return number is begin return case when start_date = end_date then 1 else end_date - start_date end; end; data as ( select date'2019-07-01' start_date, date'2019-07-01' end_date from dual union all select date'2019-07-01' start_date, date'2019-07-02' end_date from dual union all select date'2019-07-01' start_date, date'2019-07-03' end_date from dual ) select start_date, end_date, getDateDiff (start_date, end_date) diff from data / START_DATE END_DATE DIFF ------------------- ------------------- ---------- 2019-07-01 00:00:00 2019-07-01 00:00:00 1 2019-07-01 00:00:00 2019-07-02 00:00:00 1 2019-07-01 00:00:00 2019-07-03 00:00:00 2 

Объявите функцию getDateDiff() в пакете или как SQL standalone function, если её надо использовать в PL/SQL контексте.

Отслеживать
ответ дан 19 июл 2019 в 7:15
51.6k 201 201 золотой знак 63 63 серебряных знака 245 245 бронзовых знаков

• sql
• oracle
• plsql

Важное на Мете

Похожие

Подписаться на ленту

Лента вопроса

Для подписки на ленту скопируйте и вставьте эту ссылку в вашу программу для чтения RSS.

Дизайн сайта / логотип © 2024 Stack Exchange Inc; пользовательские материалы лицензированы в соответствии с CC BY-SA . rev 2024.1.3.2953

Нажимая «Принять все файлы cookie» вы соглашаетесь, что Stack Exchange может хранить файлы cookie на вашем устройстве и раскрывать информацию в соответствии с нашей Политикой в отношении файлов cookie.

Функция DATEDIFF стр. 2

Консоль

Выполнить

Результат – 100 минут.

Примечание:

В запросе используется стандартное представление даты (ISO) в виде текстовой строки ‘yyyy-mm-ddThh:mm:ss’, которое не зависит ни от локальных настроек сервера, ни и от самого сервера.

MySQL

Функция DATEDIFF есть и в MySQL, однако она имеет совсем другой смысл. DATEDIFF вычисляет число дней между двумя датами, являющихся аргументами этой функции. При этом если дата представлена в формате дата-время, используется только составляющая даты. Поэтому все три нижепредставленных запроса дадут один и тот же результат -1. Положительный результат будет получен, если первый аргумент больше второго.

Один день мы получим даже в случае, если интервал между датами составляет все одну секунду:

Решение же нашей задачи можно получить при помощи другой встроенной функции – TIMESTAMPDIFF, которая аналогичная функции DATEDIFF в Язык структурированных запросов) — универсальный компьютерный язык, применяемый для создания, модификации и управления данными в реляционных базах данных. SQL Server:

PostgreSQL

В PostgreSQL нет функции, подобной DATEDIFF (SQL Server) или TIMESTAMPDIFF (MySQL). Поэтому для решения задачи можно применить следующую последовательность действий:

1. представить разность между двумя датами интервалом;
2. посчитать число секунд в интервале;
3. поделить полученное на шаге 2 значение на 60, чтобы выразить результат в минутах.

Для получения интервала можно взять разность двух значений темпорального типа, При этом требуется явное преобразование типа:

или в стандартном исполнении

Результат — «01:40:00», который есть не что иное, как один час и сорок минут.

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

Для получения числа секунд в интервале воспользуемся функцией

Значение интервала представим последним способом как наиболее коротким. Для получения окончательного решения задачи поделим полученный результат на 60:

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

Oracle

Oracle тоже не имеет в своем арсенале функции типа DATEDIFF. Кроме того, Oracle не поддерживает стандартное текстовое представление даты/времени.

Мы можем посчитать интервал в минутах, приняв во внимание, что разность дат (значений типа date ) в Oracle дает в результате число дней (суток). Тогда для вычисления интервала в минутах нужно просто разность дат умножить на 24 (число часов в сутках), а затем на 60 (число минут в часе):

Подобным образом можно получить и другие временные интервалы.