Функции Transact-SQL для обработки даты/времени
Стандарт SQL-92 специфицирует только функции, возвращающие системную дату/время. Например, функция CURRENT_TIMESTAMP возвращает сразу и дату, и время. Плюс имеются функции возвращающие что-либо одно.
Естественно, в силу такой ограниченности, реализации языка расширяют стандарт за счет добавления функций, облегчающий работу пользователей с данными этого типа. Здесь мы рассмотрим функции обработки даты/времени в T-SQL.
Функция DATEADD
DATEADD ( datepart , number, date )
Эта функция возвращает значение типа datetime, которое получается добавлением к дате date количества интервалов типа datepart, равного number. Например, мы можем к заданной дате добавить любое число лет, дней, часов, минут и т.д. Допустимые значения аргумента datepart приведены ниже и взяты из BOL.
| Datepart | Допустимые сокращения |
| Year — год | yy, yyyy |
| Quarter — квартал | qq, q |
| Month — месяц | mm, m |
| Dayofyear — день года | dy, y |
| Day — день | dd, d |
| Week — неделя | wk, ww |
| Hour — час | hh |
| Minute — минута | mi, n |
| Second — секунда | ss, s |
| Millisecond — миллисекунда | ms |
Пусть сегодня 23/01/2004, и мы хотим узнать, какой день будет через неделю. Мы можем написать
| SELECT DATEADD(day, 7, current_timestamp) |
а можем и так
| SELECT DATEADD(ww, 1, current_timestamp) |
В результате получим одно и то же; что-то типа 2004-01-30 19:40:58.923.
Однако мы не можем в этом случае написать
| SELECT DATEADD(mm, 1/4, current_timestamp) |
потому, что дробная часть значения аргумента datepart отбрасывается, и мы получим 0 вместо одной четвертой и, как следствие, текущий день.
Кроме того, мы можем использовать вместо CURRENT_TIMESTAMP функцию T-SQL GETDATE() с тем же самым эффектом. Наличие двух идентичных функций поддерживается, видимо, в ожидании последующего развития стандарта.
Пример (схема 4). Определить, какой будет день через неделю после последнего полета.
| SELECT DATEADD(day, 7, (SELECT MAX(date) max_date FROM pass_in_trip)) |
Использование подзапроса в качестве аргумента допустимо, т.к. этот подзапрос возвращает ЕДИНСТВЕННОЕ значение типа datetime.
Функция DATEDIFF
DATEDIFF ( datepart , startdate , enddate )
Функция возвращает интервал времени, прошедшего между двумя временными отметками — startdate (начальная отметка) и enddate (конечная отметка). Этот интервал может быть измерен в разных единицах. Возможные варианты определяются аргументом datepart и перечислены выше применительно к функции DATEADD.
Пример (схема 4). Определить количество дней, прошедших между первым и последним совершенными рейсами.
| SELECT DATEDIFF(dd, (SELECT MIN(date) FROM pass_in_trip), (SELECT MAX(date) FROM pass_in_trip)) |
Пример (схема 4). Определить продолжительность рейса 1123 в минутах.
Здесь следует принять во внимание, что время вылета (time_out) и время прилета (time_in) хранится в полях типа datetime таблицы Trip. Заметим, что SQL Server вплоть до версии 2000 не имеет отдельных темпоральных типов данных для даты и времени, появление которых ожидается в следующей версии (Yukon). Поэтому при вставке в поле datetime только времени (например, UPDATE trip SET time_out = ’17:24:00′ WHERE trip_no=1123), время будет дополнено значением даты по умолчанию (‘1900-01-01’).
Напрашивающееся решение
| SELECT DATEDIFF(mi, time_out, time_in) dur FROM trip WHERE trip_no=1123, |
(которое дает -760) будет неверным по двум причинам.
Во-первых, для рейсов, которые вылетают в один день, а прилетают на следующий, вычисленное таким способом значение будет неправильным. Во-вторых, ненадежно делать какие либо предположения относительно дня, который присутствует только в силу необходимости соответствовать типу datetime.
Но как определить, что самолет приземлился на следующий день? Тут помогает описание предметной области, где говорится, что полет не может продолжаться более суток. Итак, если время прилета не больше, чем время вылета, то этот факт имеет место. Теперь второй вопрос: как посчитать только время, с каким бы днем оно ни стояло?
Здесь может помочь функция T-SQL DATEPART.
Функция DATEPART
DATEPART ( datepart , date )
Эта функция возвращает целое число, представляющее собой указанную аргументом datepart часть заданной вторым аргументом даты (date).
Список допустимых значений аргумента datepart, описанный выше в данном разделе, дополняется еще одним значением
| Datepart | Допустимые сокращения |
| Weekday — день недели | dw |
Заметим, что возвращаемое функцией DATEPART значение в этом случае (номер дня недели) зависит от настроек, которые можно изменить с помощью оператора SET DATEFIRST, устанавливающего первый день недели. Для кого-то понедельник — день тяжелый, а для кого-то — воскресенье. Кстати, последнее значение принимается по умолчанию.
Однако вернемся к нашему примеру. В предположении, что время вылета/прилета является кратным минуте, мы можем его определить как сумму часов и минут. Поскольку функции даты/времени работают с целочисленными значениями, приведем результат к наименьшему интервалу — минутам. Итак, время вылета рейса 1123 в минутах
| SELECT DATEPART(hh, time_out)*60 + DATEPART(mi, time_out) FROM trip WHERE trip_no=1123 |
и время прилета
| SELECT DATEPART(hh, time_in)*60 + DATEPART(mi, time_in) FROM trip WHERE trip_no=1123 |
Теперь мы должны сравнить, превышает ли время прилета время вылета. Если это так, вычесть из первого второе, чтобы получить продолжительность рейса. В противном случае к разности нужно добавить одни сутки (24*60 = 1440 минут).
| SELECT CASE WHEN time_dep>=time_arr THEN time_arr-time_dep+1440 ELSE time_arr-time_dep END dur FROM ( SELECT DATEPART(hh, time_out)*60 + DATEPART(mi, time_out) time_dep, DATEPART(hh, time_in)*60 + DATEPART(mi, time_in) time_arr FROM trip WHERE trip_no=1123 ) tm |
Здесь, чтобы не повторять длинные конструкции в операторе CASE, использован подзапрос. Конечно, результат получился достаточно громоздким, зато абсолютно корректным в свете сделанных к этой задаче замечаний.
Пример (4 схема). Определить дату и время вылета рейса 1123.
В таблице совершенных рейсов Pass_in_trip содержится только дата рейса, но не время, т.к. в соответствии с предметной областью каждый рейс может выполняться только один раз в день. Для решения этой задачи нужно к дате, хранящейся в таблице Pass_in_trip, добавить время из таблицы Trip
| SELECT pt.trip_no, DATEADD(mi, DATEPART(hh,time_out)*60 + DATEPART(mi,time_out), date) [time] FROM pass_in_trip pt JOIN trip t ON pt.trip_no=t.trip_no WHERE t.trip_no=1123 |
Выполнив запрос, получим следующий результат
| Trip_no | Time |
| 1123 | 2003-04-05 16:20:00.000 |
| 1123 | 2003-04-08 16:20:00.000 |
DISTINCT необходим здесь, чтобы исключить возможные дубликаты, поскольку номер и дата рейса дублируются в этой таблице для каждого пассажира данного рейса.
Функция DATENAME
DATENAME ( datepart , date )
Эта функция возвращает символьное представление составляющей (datepart ) указанной даты (date). Аргумент, определяющий составляющую даты, может принимать одно из значений, перечисленных в вышеприведенной таблице.
Это дает нам простую возможность конкатенировать компоненты даты, получая любой нужный формат представления. Например, конструкция
| SELECT DATENAME ( weekday , ‘2003-12-31′ )+’, ‘+DATENAME ( day , ‘2003-12-31′ )+’ ‘+ DATENAME ( month , ‘2003-12-31′ )+’ ‘+DATENAME ( year , ‘2003-12-31’ ) |
даст нам следующий результат
| Wednesday, 31 December 2003 |
Следует отметить, что данная функция выявляет отличие значений day и dayofyear аргумента datepart. Первый дает символьное представление дня указанной даты, в то время как второй дает символьное представление этого дня от начала года. Т.е.
| SELECT DATENAME ( day , ‘2003-12-31’ ) |
даст нам 31, а
| SELECT DATENAME ( dayofyear , ‘2003-12-31’ ) |
— 365.
В ряде случаев функцию DATEPART можно заменить более простыми функциями. Вот они:
DAY ( date ) — целочисленное представление дня указанной даты. Эта функция эквивалентна функции DATEPART(dd, date).
MONTH ( date ) — целочисленное представление месяца указанной даты. Эта функция эквивалентна функции DATEPART(mm, date).
YEAR ( date ) — целочисленное представление года указанной даты. Эта функция эквивалентна функции DATEPART(yy, date).
Функция @@DATEFIRST
@@DATEFIRST возвращает число, которое определяет первый день недели, установленный для текущей сессии. При этом 1 соответствует понедельнику, а 7, соответственно, воскресенью. Т.е. если
Источник
Функция DATEPART стр. 1
Эта функция возвращает целое число, представляющее собой указанную аргументом datepart часть заданной вторым аргументом даты (date).
Список допустимых значений аргумента datepart, описанный выше в данном разделе, дополняется еще одним значением
|
Заметим, что возвращаемое функцией DATEPART значение в этом случае (номер дня недели) зависит от настроек, которые можно изменить с помощью оператора SET DATEFIRST , устанавливающего первый день недели. Для кого-то понедельник — день тяжелый, а для кого-то — воскресенье. Как и многие региональные настройки, по умолчанию это значение в 
Cистема управления реляционными базами данных (СУБД), разработанная корпорацией Microsoft. Язык структурированных запросов) — универсальный компьютерный язык, применяемый для создания, модификации и управления данными в реляционных базах данных. SQL Server зависит от выбранного языка.
Однако вернемся к примеру 7.1.3. В предположении, что время вылета/прилета является кратным минуте, мы можем его определить как сумму часов и минут. Поскольку функции даты/времени работают с целочисленными значениями, приведем результат к наименьшему интервалу — минутам. Итак, время вылета рейса 1123 в минутах:
и время прилета
Теперь мы должны сравнить, превышает ли время прилета время вылета. Если это так, следует вычесть из первого второе, чтобы получить продолжительность рейса. В противном случае к разности нужно добавить одни сутки (24*60 = 1440 минут).
Здесь, чтобы не повторять длинные конструкции в операторе CASE , использован подзапрос. Конечно, результат получился достаточно громоздким, зато абсолютно корректным в свете сделанных к этой задаче замечаний.
Определить дату и время вылета рейса 1123.
В таблице совершенных рейсов Pass_in_trip содержится только дата рейса, но не время, так как в соответствии с предметной областью каждый рейс может выполняться только один раз в день. Для решения этой задачи нужно к дате, хранящейся в таблице Pass_in_trip, добавить время из таблицы Trip
Выполнив запрос, получим следующий результат
|
DISTINCT необходим здесь, чтобы исключить возможные дубликаты, поскольку номер и дата рейса дублируются в этой таблице для каждого пассажира данного рейса.
В ряде случаев функцию DATEPART можно заменить более простыми функциями. Вот они:
DAY (date) — целочисленное представление дня указанной даты. Эта функция эквивалентна функции DATEPART(dd, date).
MONTH (date) — целочисленное представление месяца указанной даты. Эта функция эквивалентна функции DATEPART(mm, date).
YEAR (date) — целочисленное представление года указанной даты. Эта функция эквивалентна функции DATEPART(yy, date).
Источник
Получить имя дня недели с заданной даты в SQL Server
Я пытаюсь получить название дня, как пятница, суббота, воскресенье, понедельник и т. д. от указанной даты. Я знаю, что есть встроенная функция, которая возвращает название дня, например:
этот SQL-запрос возвращает:
‘понедельник’
все в порядке. Но я хотел бы пройти Month, Day and Year индивидуально.
Я использую встроенную функцию DATEPART для извлечения месяца, дня и года из даты, чтобы я мог передать ее в имя даты функция:
теперь, когда у меня есть месяц, день, год значения индивидуально, я передаю его в мой DATENAME для получения Weekname даты, которую я хочу:
это возвращает неправильное имя дня. Я попытался заменить / с — так, что в функции DATENAME мой SQL-запрос становится:
но он по-прежнему возвращает неверное имя дня из моего SQL-запроса. Я что-то упускаю.
6 ответов
вам нужно построить строку даты. Вы используете / или — операторы, которые выполняют математические / числовые операции над числовыми возвращаемыми значениями DATEPART. Тогда DATENAME принимает это числовое значение и интерпретирует его как дату.
нужно преобразовать его в строку. Например:
протестировано и работает на SQL 2005 и 2008. Не уверен, если это работает в 2012 году и позже.
решение использует DATENAME вместо DATEPART
если у вас есть SQL Server 2012:
если ваши части даты являются целыми числами, вы можете использовать DATEFROMPARTS .
если ваши части даты являются строками, то вы можете использовать CONCAT .
Источник
