Функция DATEADD стр. 1
Функция DATEADD (datepart, number, date) возвращает значение типа datetime, которое получается добавлением к дате date количества интервалов типа datepart, равного number (целое число).
Например, мы можем к заданной дате добавить любое число лет, дней, часов, минут и т. д.
Допустимые значения аргумента datepart приведены ниже в таблице и взяты из электронной документации к Cистема управления реляционными базами данных (СУБД), разработанная корпорацией Microsoft. Язык структурированных запросов) — универсальный компьютерный язык, применяемый для создания, модификации и управления данными в реляционных базах данных. SQL Server — Books On Line (BOL).
Пусть сегодня 28.10.2005, и мы хотим узнать, какой день будет через неделю. Мы можем написать:
Консоль
Выполнить
Консоль
Выполнить
В результате получим одно и то же значение; что-то типа 2005-11-04 00:11:28.683.
Однако мы не можем в этом случае написать:
Консоль
Выполнить
и не потому, что четверть месяца не равна в точности неделе, а потому, что дробная часть значения аргумента datepart отбрасывается, и мы получим 0 вместо одной четвертой и, как следствие, текущий день.
Кроме того, мы можем использовать вместо CURRENT_TIMESTAMP функцию T-SQL (Transact-SQL) — процедурное расширение языка SQL, используемое для программирования на стороне сервера в Microsoft SQL Server и Sybase ASE. T-SQL GETDATE () с тем же самым эффектом. Наличие двух идентичных функций поддерживается, видимо, в ожидании последующего развития стандарта.
Определить, какой будет день через неделю после последнего полета.
Примечание:
В примерах данной главы используется база данных «Аэрофлот». Описание этой схемы и всех остальных схем, используемых в настоящее время на сайте для решения задач, можно найти в Примечании 1.
Консоль
Выполнить
Команда INTERVAL
Команда INTERVAL позволяет прибавлять к дате и отнимать от нее определенные промежутки времени.
После команды INTERVAL можно указывать определенную часть даты (день, месяц или год и тп), к примеру, так INTERVAL 1 DAY или INTERVAL 3 MONTH, или сразу несколько частей.
В этом случае значения даты берутся в кавычки, пример: давайте прибавим 1 год и 3 месяца — это будет выглядеть так: INTERVAL ‘1-3’ YEAR_MONTH.
Мы указываем, что хотим год и месяц (вот так: YEAR_MONTH), и перед этим в кавычках пишем сначала значение для года, потом значение для месяца. Между ними ставим разделитель (он может быть абсолютно любым).
Синтаксис
SELECT дата + INTERVAL что_прибавить FROM имя_таблицы WHERE условие
SELECT дата — INTERVAL что_отнять FROM имя_таблицы WHERE условие
Команды
- SECOND секунды
- MINUTE минуты
- HOUR часы
- DAY дни
- MONTH месяцы
- YEAR года
- MINUTE_SECOND минуты:секунды
- HOUR_MINUTE часы:минуты
- DAY_HOUR дни часы
- YEAR_MONTH года-месяцы
- HOUR_SECOND часы:минуты:секунды
- DAY_MINUTE дни часы:минуты
- DAY_SECOND дни часы:минуты:секунды
Таблицы для примеров
| id айди |
name событие |
datetime дата и время |
|---|---|---|
| 1 | событие 1 | 2010-03-01 12:40:50 |
| 2 | событие 2 | 2011-04-02 13:41:51 |
| 3 | событие 3 | 2012-05-03 14:42:52 |
Пример
В данном примере при выборке из таблицы к дате прибавляется 1 день:
SELECT *, datetime + INTERVAL 1 DAY as new_date FROM events
Результат выполнения кода:
| id айди |
name событие |
datetime дата |
new_date новая дата |
|---|---|---|---|
| 1 | событие 1 | 2010-03-01 12:01:02 | 2010-03- 02 12:01:02 |
| 2 | событие 2 | 2011-04-02 13:02:03 | 2011-05- 03 13:02:03 |
| 3 | событие 3 | 2012-05-03 14:03:04 | 2012-06- 04 14:03:04 |
Пример
В данном примере от даты отнимается 1 день:
SELECT *, datetime — INTERVAL 1 DAY as new_date FROM events
Результат выполнения кода:
| id айди |
name имя |
date дата |
new_date новая дата |
|---|---|---|---|
| 1 | user1 | 2010-03-01 12:01:02 | 2010- 02-28 12:01:02 |
| 2 | user2 | 2011-04-02 13:02:03 | 2011-05- 01 13:02:03 |
| 3 | user3 | 2012-05-03 14:03:04 | 2012-06- 02 14:03:04 |
Пример
В данном примере к дате прибавляется 1 год и 2 месяца:
SELECT *, date + INTERVAL «1-2» YEAR_MONTH as new_date FROM events
Результат выполнения кода:
| id айди |
name имя |
date дата |
new_date новая дата |
|---|---|---|---|
| 1 | user1 | 2010-03-01 12:01:02 | 2011-05 -01 12:01:02 |
| 2 | user2 | 2011-04-02 13:02:03 | 2012-06 -02 13:02:03 |
| 3 | user3 | 2012-05-03 14:03:04 | 2013-07 -03 14:03:04 |
Разделитель в запросе не имеет значения. Он может быть любым символом:
SELECT *, date + INTERVAL «1:2» YEAR_MONTH as new_date FROM events
Можно использовать даже несколько символов:
SELECT *, date + INTERVAL «1. 2» YEAR_MONTH as new_date FROM events
Можно использовать и пробел:
SELECT *, date + INTERVAL «1 2» YEAR_MONTH as new_date FROM events
Пример
В данном примере к дате прибавляется 1 год, 2 месяца и 1 день:
SELECT *, date + INTERVAL 1 YEAR + INTERVAL 2 MONTH + INTERVAL 1 DAY as new_date FROM employees
Результат выполнения кода:
| id айди |
name имя |
date дата |
new_date новая дата |
|---|---|---|---|
| 1 | user1 | 2010-03-01 12:01:02 | 2011-05-02 12:01:02 |
| 2 | user2 | 2011-04-02 13:02:03 | 2012-06-03 13:02:03 |
| 3 | user3 | 2012-05-03 14:03:04 | 2013-07-04 14:03:04 |
Запрос можно переписать таким образом:
SELECT *, date + INTERVAL «1 2» YEAR_MONTH + INTERVAL 1 DAY as new_date FROM events
Пример
В данном примере к дате прибавляется 1 год, отнимаются 2 месяца и прибавляется 1 день:
SELECT *, date + INTERVAL 1 YEAR — INTERVAL 2 MONTH + INTERVAL 1 DAY as new_date FROM events
Результат выполнения кода:
| id айди |
name имя |
date дата |
new_date новая дата |
|---|---|---|---|
| 1 | user1 | 2010-03-01 12:01:02 | 2011-01-02 12:01:02 |
| 2 | user2 | 2011-04-02 13:02:03 | 2012-02-03 13:02:03 |
| 3 | user3 | 2012-05-03 14:03:04 | 2013-03-04 14:03:04 |
Смотрите также
- функцию DATE_ADD ,
которая прибавляет значение к дате - функцию DATE_SUB ,
которая вычитает значение из даты
DATEADD (Transact-SQL)
Эта функция добавляет number (целое число со знаком) в область datepartдаты ввода и возвращает измененное значение даты или времени. Например, эту функцию можно использовать для поиска даты, которая отстоит на 7000 минут от сегодняшнего дня: number = 7000, datepart = минута, date = сегодняшняя дата.
Обзор всех типов данных и функций даты и времени в языке Transact-SQL см. в статье Типы данных и функции даты и времени (Transact-SQL).
Синтаксис
DATEADD (datepart , number , date ) Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.
Аргументы
datepart
Компонент даты date, к которому DATEADD добавляет integernumber. В приведенной ниже таблице перечислены все допустимые аргументы datepart.
DATEADD не принимает эквивалентные переменные, определяемые пользователем, для аргументов datepart.
| datepart | 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 |
number
Выражение, которое разрешается в тип int, добавляемый DATEADD к компоненту datepart даты date. DATEADD принимает определяемые пользователем значения переменных для number. DATEADD Усечение указанного числа , которое имеет десятичную дробь. В этой ситуации значение number не округляется.
date
Выражение, которое может быть разрешено в одно из следующих значений.
Для date DATEADD будет принимать столбец выражения, выражение, строковый литерал или определяемую пользователем переменную. Значение строкового литерала должно разрешаться в datetime. Во избежание неоднозначности используйте четырехзначную запись года. Сведения о двузначном обозначении года см. в статье Настройка параметра конфигурации сервера two digit year cutoff.
Типы возвращаемых данных
Тип данных возвращаемого значения для этого метода является динамическим. Тип возвращаемого значения зависит от типа аргумента, переданного в date . Если значение для date является строковым литералом даты, DATEADD возвращает значение datetime. Если для date предоставляется другой тип допустимых входных данных, DATEADD возвращает тот же тип данных. Если строковый литерал имеет более трех позиций долей секунды (.nnn) или если строковый литерал содержит часть смещения часового пояса, DATEADD выдаст ошибку.
Возвращаемое значение
Аргумент datepart
Функции dayofyear, day и weekday возвращают одинаковое значение.
Каждое выражение datepart и его краткие формы возвращают одно и то же значение.
Если верны следующие условия:
- datepart имеет значение month;
- в месяце date больше дней, чем в возвращаемом месяце;
- день date не существует в этом месяце;
то DATEADD возвращает последний день возвращаемого месяца. Например, сентябрь имеет 30 (тридцать) дней; Поэтому эти операторы возвращают значение «2006-09-30 00:00:00.000»:
SELECT DATEADD(month, 1, '20060830'); SELECT DATEADD(month, 1, '2006-08-31'); Аргумент number
Аргумент числа не может превышать диапазон int. В следующих инструкциях аргумент числа превышает диапазон int на 1. Эти операторы возвращают следующее сообщение об ошибке: Msg 8115, Level 16, State 2, Line 1. Arithmetic overflow error converting expression to data type int.
SELECT DATEADD(year,2147483648, '20060731'); SELECT DATEADD(year,-2147483649, '20060731'); Аргумент date
DATEADD не будет принимать аргумент date, увеличенный до значения, выходящего за диапазон соответствующего типа данных. В приведенных ниже инструкциях значение number, добавленное к значению date, превышает диапазон типа данных date. DATEADD возвращает следующее сообщение об ошибке: Msg 517, Level 16, State 1, Line 1 Adding a value to a ‘datetime’ column caused overflow.
SELECT DATEADD(year,2147483647, '20060731'); SELECT DATEADD(year,-2147483647, '20060731'); Возвращаемые значения дат с типом данных smalldatetime и частью даты в виде секунд или долей секунды.
Значение секунд даты типа smalldatetime всегда равно 00. Для значения date типа smalldatetime действуют указанные ниже условия.
- Для части даты datepart секунды second и значения number в диапазоне от –30 до +29 DATEADD не вносит никаких изменений.
- Для части даты datepart секунды second и значения number меньше –30 или больше +29 DATEADD выполняет добавление, начиная с одной минуты.
- Для части даты datepart миллисекунды millisecond и значения number в диапазоне от –30 001 до + 29 998 DATEADD не вносит никаких изменений.
- Для части даты datepart миллисекунды millisecond и значения number меньше –30 001 или больше +29 998 DATEADD выполняет добавление, начиная с одной минуты.
Замечания
Используйте DATEADD в следующих предложениях.
Точность в долях секунды
DATEADD не допускает использование при сложении в качестве аргумента datepart значений microsecond или nanosecond для типов данных date: smalldatetime, date и datetime.
Миллисекунды имеют точность 3 знака (0,123), микросекунды — 6 знаков (0,123456), наносекунды — 9 знаков (0,123456789). Типы данных time, datetime2 и datetimeoffset имеют максимальную точность 7 знаков (0,1234567). Если аргументом datepart является nanosecond, аргумент number должен иметь значение 100 перед увеличением даты date на доли секунды. number от 1 до 49 округляется до 0, а number от 50 до 99 округляется до 100.
Эти инструкции добавляют часть даты datepart: millisecond, microsecond или nanosecond.
DECLARE @datetime2 datetime2 = '2007-01-01 13:10:10.1111111'; SELECT '1 millisecond', DATEADD(millisecond,1,@datetime2) UNION ALL SELECT '2 milliseconds', DATEADD(millisecond,2,@datetime2) UNION ALL SELECT '1 microsecond', DATEADD(microsecond,1,@datetime2) UNION ALL SELECT '2 microseconds', DATEADD(microsecond,2,@datetime2) UNION ALL SELECT '49 nanoseconds', DATEADD(nanosecond,49,@datetime2) UNION ALL SELECT '50 nanoseconds', DATEADD(nanosecond,50,@datetime2) UNION ALL SELECT '150 nanoseconds', DATEADD(nanosecond,150,@datetime2); 1 millisecond 2007-01-01 13:10:10.1121111 2 milliseconds 2007-01-01 13:10:10.1131111 1 microsecond 2007-01-01 13:10:10.1111121 2 microseconds 2007-01-01 13:10:10.1111131 49 nanoseconds 2007-01-01 13:10:10.1111111 50 nanoseconds 2007-01-01 13:10:10.1111112 150 nanoseconds 2007-01-01 13:10:10.1111113 Смещение часового пояса
DATEADD не допускает добавление для смещения часового пояса.
Примеры
А. Приращение даты на интервал 1
Каждая из этих инструкций увеличивает часть даты datepart на интервал, равный 1.
DECLARE @datetime2 datetime2 = '2007-01-01 13:10:10.1111111'; SELECT 'year', DATEADD(year,1,@datetime2) UNION ALL SELECT 'quarter',DATEADD(quarter,1,@datetime2) UNION ALL SELECT 'month',DATEADD(month,1,@datetime2) UNION ALL SELECT 'dayofyear',DATEADD(dayofyear,1,@datetime2) UNION ALL SELECT 'day',DATEADD(day,1,@datetime2) UNION ALL SELECT 'week',DATEADD(week,1,@datetime2) UNION ALL SELECT 'weekday',DATEADD(weekday,1,@datetime2) UNION ALL SELECT 'hour',DATEADD(hour,1,@datetime2) UNION ALL SELECT 'minute',DATEADD(minute,1,@datetime2) UNION ALL SELECT 'second',DATEADD(second,1,@datetime2) UNION ALL SELECT 'millisecond',DATEADD(millisecond,1,@datetime2) UNION ALL SELECT 'microsecond',DATEADD(microsecond,1,@datetime2) UNION ALL SELECT 'nanosecond',DATEADD(nanosecond,1,@datetime2); Year 2008-01-01 13:10:10.1111111 quarter 2007-04-01 13:10:10.1111111 month 2007-02-01 13:10:10.1111111 dayofyear 2007-01-02 13:10:10.1111111 day 2007-01-02 13:10:10.1111111 week 2007-01-08 13:10:10.1111111 weekday 2007-01-02 13:10:10.1111111 hour 2007-01-01 14:10:10.1111111 minute 2007-01-01 13:11:10.1111111 second 2007-01-01 13:10:11.1111111 millisecond 2007-01-01 13:10:10.1121111 microsecond 2007-01-01 13:10:10.1111121 nanosecond 2007-01-01 13:10:10.1111111 B. Увеличение нескольких уровней даты в одной инструкции
Каждая из этих инструкций увеличивает часть даты datepart на число number, достаточно большое, чтобы также увеличить следующую часть datepart даты date.
DECLARE @datetime2 datetime2; SET @datetime2 = '2007-01-01 01:01:01.1111111'; --Statement Result ------------------------------------------------------------------- SELECT DATEADD(quarter,4,@datetime2); --2008-01-01 01:01:01.1111111 SELECT DATEADD(month,13,@datetime2); --2008-02-01 01:01:01.1111111 SELECT DATEADD(dayofyear,365,@datetime2); --2008-01-01 01:01:01.1111111 SELECT DATEADD(day,365,@datetime2); --2008-01-01 01:01:01.1111111 SELECT DATEADD(week,5,@datetime2); --2007-02-05 01:01:01.1111111 SELECT DATEADD(weekday,31,@datetime2); --2007-02-01 01:01:01.1111111 SELECT DATEADD(hour,23,@datetime2); --2007-01-02 00:01:01.1111111 SELECT DATEADD(minute,59,@datetime2); --2007-01-01 02:00:01.1111111 SELECT DATEADD(second,59,@datetime2); --2007-01-01 01:02:00.1111111 SELECT DATEADD(millisecond,1,@datetime2); --2007-01-01 01:01:01.1121111 C. Использование выражений в качестве аргументов для параметров number и date
В этих примерах выражения различного типа используются в качестве аргументов для параметров number и date. В примерах используется база данных AdventureWorks.
Указание столбца в качестве аргумента date
В этом примере к каждому значению в столбце 2 добавляется OrderDate (два) дня, чтобы получить новый столбец с именем PromisedShipDate .
SELECT SalesOrderID ,OrderDate ,DATEADD(day,2,OrderDate) AS PromisedShipDate FROM Sales.SalesOrderHeader; Частичный результирующий набор имеет следующий вид:
SalesOrderID OrderDate PromisedShipDate ------------ ----------------------- ----------------------- 43659 2005-07-01 00:00:00.000 2005-07-03 00:00:00.000 43660 2005-07-01 00:00:00.000 2005-07-03 00:00:00.000 43661 2005-07-01 00:00:00.000 2005-07-03 00:00:00.000 . 43702 2005-07-02 00:00:00.000 2005-07-04 00:00:00.000 43703 2005-07-02 00:00:00.000 2005-07-04 00:00:00.000 43704 2005-07-02 00:00:00.000 2005-07-04 00:00:00.000 43705 2005-07-02 00:00:00.000 2005-07-04 00:00:00.000 43706 2005-07-03 00:00:00.000 2005-07-05 00:00:00.000 . 43711 2005-07-04 00:00:00.000 2005-07-06 00:00:00.000 43712 2005-07-04 00:00:00.000 2005-07-06 00:00:00.000 . 43740 2005-07-11 00:00:00.000 2005-07-13 00:00:00.000 43741 2005-07-12 00:00:00.000 2005-07-14 00:00:00.000 Указание пользовательских переменных в качестве аргументов number и date
В этом примере в качестве аргументов number и date указываются пользовательские переменные.
DECLARE @days INT = 365, @datetime DATETIME = '2000-01-01 01:01:01.111'; /* 2000 was a leap year */; SELECT DATEADD(day, @days, @datetime); ----------------------- 2000-12-31 01:01:01.110 (1 row(s) affected) Указание в качестве аргумента date скалярной системной функции
В этом примере для аргумента date указано значение SYSDATETIME . Точное возвращаемое значение зависит от дня и времени выполнения инструкции.
SELECT DATEADD(month, 1, SYSDATETIME()); --------------------------- 2013-02-06 14:29:59.6727944 (1 row(s) affected) Указание в качестве аргументов number и date скалярных вложенных запросов и скалярных функций
В этом примере в качестве аргументов для number и date используются скалярные вложенные запросы MAX(ModifiedDate) . (SELECT TOP 1 BusinessEntityID FROM Person.Person) является искусственным аргументом для числового параметра, показывающим способ выбора аргумента number из списка значений.
SELECT DATEADD(month,(SELECT TOP 1 BusinessEntityID FROM Person.Person), (SELECT MAX(ModifiedDate) FROM Person.Person)); Указание в качестве аргументов number и date числовых выражений и скалярных системных функций
В этом примере в качестве аргументов number и date используется числовое выражение (– (10/2)) , унарные операторы ( — ), арифметический оператор ( / ) и скалярные системные функции ( SYSDATETIME ).
SELECT DATEADD(month,-(10/2), SYSDATETIME()); Указание функций ранжирования в качестве числа
В этом примере в качестве аргумента number используется ранжирующая функция.
SELECT p.FirstName, p.LastName ,DATEADD(day,ROW_NUMBER() OVER (ORDER BY a.PostalCode),SYSDATETIME()) AS 'Row Number' FROM Sales.SalesPerson AS s INNER JOIN Person.Person AS p ON s.BusinessEntityID = p.BusinessEntityID INNER JOIN Person.Address AS a ON a.AddressID = p.BusinessEntityID WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0; Указание в качестве аргумента number статистической оконной функции
В этом примере в качестве аргумента number используется агрегатная оконная функция.
SELECT SalesOrderID, ProductID, OrderQty ,DATEADD(day,SUM(OrderQty) OVER(PARTITION BY SalesOrderID),SYSDATETIME()) AS 'Total' FROM Sales.SalesOrderDetail WHERE SalesOrderID IN(43659,43664); GO Связанный контент
Как добавить к параметру +1 день?
Может, опишете проблему поточнее? Зачем вам такое понадобилось? Подозреваю, что связано с тем, что в MdifiedAt значение хранится дата с временем.
15 мар 2016 в 5:54
@Vasil Baymurzin, Да в ModifiedAt значение хранится дата с временем. Если я беру ModifiedAt between StartDate and EndDate то не попадает записи у которых дата к примеру 2016-02-29 18:15:42.087
16 мар 2016 в 6:27
как вариант, можно в условии у ModifiedAt отсечь время, но при этом при наличии индексов по этому полю, если не ошибаюсь, будет не очень эффективно.
16 мар 2016 в 10:46
1 ответ 1
Сортировка: Сброс на вариант по умолчанию
С помощью функции dateadd :
Where ModifiedAt between @StartDate and dateadd(day, 1, @EndDate) Можно добавлять день, час, месяц, год, неделю и т.п. — это задаётся первым параметром. Второй параметр — сколько соответствующих интервалов добавить. Последний — то к чему добавлять. Замечу, что можно добавлять отрицательные величины.
Вижу, у вас это в процедуре с параметром, иногда удобнее прибавлять день к параметру перед вызовом процедуры (например, если происходит вызов из внешнего приложения), оставив в ней:
Where ModifiedAt between @StartDate and @EndDate но, это конечно — как вам удобнее.
Да, и, для дат часто делают проверку интервала с нижней границей включённой, а верхней — исключенной, чтобы если сделка совершена ровно в промежутке, она не попадала в два интервала одновременно:
Where ModifiedAt >= @StartDate and ModifiedAt < @EndDate T.е. если у нас допустим @StartDate = 2016-01-01 a @EndDate = 2016-03-01 , то возьмутся записи с 2016-01-01 00:00:00.000 примерно по 2016-02-29 23:59:59 .