Что делают в sql текущая дата и другие функции даты и времени

Точность в долях секунды

не допускает использование при сложении в качестве аргумента 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.

Результирующий набор:

SQL convert date to integer

If you use the CONVERT or CAST to convert a datetime to integer, it will return the number of days since 1900 until the date provided.

For example, the following T-SQL code will show the number of days from 1900 until today:

1	SELECTCONVERT(INT,GETDATE())

You can also convert to integer the year, months, days, etc. of a datetime value. The following code shows how to store in integer variables the day, month and year of a datetime value:

1 2 3 4 5

declare@yearint=year(getdate()) declare@monthint=month(getdate()) declare@dayint=day(getdate()) select@yearasyear,@monthasmonth,@dayasday

Преобразование данных типа Date и Time

При преобразовании в типы данных даты и времени SQL Server отвергает все значения, которые он не распознает как значения даты или времени. Сведения об использовании функций CAST и CONVERT c данными типов даты и времени см. в статье Функции CAST и CONVERT (Transact-SQL).

Преобразование типа данных datetimeoffset в другие типы данных даты и времени

Этот раздел описывает, что происходит при преобразовании типа данных datetimeoffset в другие типы даты и времени.

При преобразовании в тип date копируются год, месяц и день. Следующий код демонстрирует результаты преобразования значения в значение .

При преобразовании в тип time(n) копируются час, минута, секунда и доли секунды. Значение часового пояса усекается. Если точность значения типа datetimeoffset(n) больше точности значения типа time(n), оно округляется в сторону увеличения. Следующий код демонстрирует результаты преобразования значения в значение .

При преобразовании в тип datetime значения даты и времени копируются; часовой пояс усекается. Если точность в долях секунды для значения datetimeoffset(n) превышает три разряда, значение будет усечено. Следующий код демонстрирует результаты преобразования значения в значение .

При преобразовании в тип smalldatetime копируются дата и час. Минуты округляются в сторону увеличения с учетом значения секунд, а секунды устанавливаются в 0. Следующий код демонстрирует результаты преобразования значения в значение .

При преобразовании в тип datetime2(n) дата и время копируются в значение datetime2, а часовой пояс усекается. Если точность значения типа datetime2(n) больше точности значения типа datetimeoffset(n), доли секунды усекаются. Следующий код демонстрирует результаты преобразования значения в значение .

Преобразование строковых литералов в datetimeoffset

Преобразование строковых литералов в типы данных даты и времени разрешается, если все части строк записаны в допустимом формате. Иначе возникает ошибка времени выполнения. Явные или скрытые преобразования, в которых не задан стиль преобразования типов данных даты и времени в строковые литералы, будут проведены в формате по умолчанию для текущего сеанса. В таблице ниже приводятся правила преобразования строковых литералов в тип данных datetimeoffset.

Строковый литерал входа	datetimeoffset(n)
ODBC DATE	Строковые литералы ODBC сопоставляются с типом данных datetime. Любая операция присваивания литералов ODBC DATETIME типам данных datetimeoffset вызывает неявное преобразование между данным типом и типом datetime согласно правилам преобразования.
ODBC TIME	См. предыдущее правило ODBC DATE.
ODBC DATETIME	См. предыдущее правило ODBC DATE.
только DATE	Компонент TIME по умолчанию имеет значение 00:00:00. Компонент TIMEZONE по умолчанию имеет значение +00:00.
только TIME	Компонент DATE по умолчанию имеет значение 1900-1-1. Компонент TIMEZONE по умолчанию будет иметь значение +00:00.
только TIMEZONE	Указаны значения по умолчанию
DATE + TIME	Компонент TIMEZONE по умолчанию имеет значение +00:00.
DATE + TIMEZONE	Нельзя использовать
TIME + TIMEZONE	Компонент DATE по умолчанию имеет значение 1900-1-1.
DATE + TIME + TIMEZONE	Простейший.

How to convert sql date into different formats in T-SQL

The following example shows how to convert the date format in different formats.

For Japananes format:

1	selectCONVERT(nvarchar(30),getdate(),111)asJapanformat

For USA format:

1	selectCONVERT(nvarchar(30),getdate(),110)asUSAformat

For ANSI format:

1	selectCONVERT(nvarchar(30),getdate(),102)asANSIformat

For British format:

1	selectCONVERT(nvarchar(30),getdate(),103)asBritishformat

For German format:

1	selectCONVERT(nvarchar(30),getdate(),104)asGermanformat

For Italian format:

1	selectCONVERT(nvarchar(30),getdate(),105)asItalianformat

For European default format:

1	selectCONVERT(nvarchar(30),getdate(),113)asEuropeDefaultformat

For ODBC Canonical:

1	selectCONVERT(nvarchar(30),getdate(),120)asODBCCanonicalformat

You always have the option to use the FORMAT function to get the date in the format that you want:

1	SELECTFORMAT(getdate(),’dd/MM/yy’)

FORMAT is easier to handle dates and use the format of your preference, because you do not need to know the style. However, in my experience I see a lot of code using the CAST and CONVERT functions so, it is better to know them.

Поддерживаемые форматы строковых литералов для типа данных datetime

В представленных ниже таблицах приводятся поддерживаемые форматы строковых литералов для типа данных datetime. За исключением ODBC, строковые литералы типа datetime заключаются в одинарные кавычки (‘), например ‘string_literaL’. Если язык среды не us_english, строковые литералы должны иметь формат N’string_literaL’.

Числовой

Описание

Форматы даты 4/15/96 — (мдг) 4-15-96 — (мдг) 4.15.96 — (мдг) 4/96/15 — (мгд) 15/4/96 — (дмг) 15/96/4 — (дгм) 96/15/4 — (гдм) 96/4/15 — (гмд) Форматы времени 14:30 14:30 14:30 4am 16:00:00

Месяц в датах может указываться в числовом формате. Например, 5/20/97 обозначает двадцатое мая 1997 года. Используя числовой формат дат, указывайте месяц, день и год в виде строки со знаками косой черты (/), дефисами (-) или точками (.) в качестве разделителей. Эта строчка должна выглядеть следующим образом:число разделитель число разделитель число При использовании языковой настройки us_english порядком по умолчанию для даты является mdy (МДГ). Порядок даты можно изменить с помощью инструкции SET DATEFORMAT. Установка для SET DATEFORMAT определяет, как будут интерпретированы значения дат. Если порядок не соответствует конфигурации, значения не обрабатываются как даты. Неупорядоченные даты могут неправильно интерпретироваться как даты за пределами диапазона или даты с неправильными значениями. Например, 12/10/08 может быть интерпретировано как одна из шести различных дат в зависимости от установки DATEFORMAT. Четырехзначный год интерпретируется как год.

Алфавитный формат

Описание

Apr 1996 Apr 15 96 Apr 1996 Apr 1996 15 Apr96 15 96 apr 1996 apr 1996 APR 1996 APR

Можно указывать дату с полным именем месяца. Например, «апрель» или сокращение «Апр», заданное в текущем языке. Запятые не обязательны, регистр букв не учитывается. Некоторые рекомендации по применению алфавитных форматов даты: 1. Заключайте дату и время в одинарные кавычки (‘). Для всех языков, кроме английского, используйте «N’». 2. Символы, заключенные в квадратные скобки, являются необязательными. 3. Если указать две последние цифры года, значения, меньшие двух последних цифр значения параметра конфигурации сервера two digit year cutoff, будут относиться к столетию года усечения. Значения, большие или равные двум последним цифрам этого параметра, относятся к столетию, предшествующему столетию года усечения. Например, если значение параметра two digit year cutoff равно 2050 (по умолчанию), то год, обозначенный двумя цифрами 25, интерпретируется как 2025, а год, обозначенный двумя цифрами 50, — как 1950. Во избежание неоднозначности используйте четырехзначную запись года. 4. Если не указано число месяца, подразумевается первое число месяца. Параметр сеанса SET DATEFORMAT не применяется, если месяц указывается в алфавитном формате.

ISO 8601

Описание

ГГГГ-ММ-ДДТчч:мм:сс ГГГГММДД]

Примеры: 1. 2004-05-23T14:25:10 2. 2004-05-23T14:25:10.487 Чтобы использовать формат ISO 8601, необходимо указать каждый элемент в этом формате, включая T, двоеточие (:) и точку (.), которые отображаются в этом формате. Квадратные скобки показывают, что доли секунд не являются обязательными. Временной компонент указан в 24-часовом формате. Символ T указывает на начало временной части значения datetime. Преимущество формата ISO 8601 заключается в том, что он является международным стандартом с однозначным представлением. Кроме того, на этот формат не влияют настройки SET DATEFORMAT и SET LANGUAGE.

Без разделителей	Описание
ГГГГММДД чч:мм:сс

ODBC

Описание

{ ts ‘1998-05-02 01:23:56.123’ } { d ‘1990-10-02′ } { t ’13:33:41’ }

Интерфейс API ODBC определяет управляющие последовательности, представляющие значения даты и времени, называемые в ODBC метками времени. Этот формат меток времени ODBC также поддерживается определением языка OLE DB (DBGUID-SQL), поддерживаемым поставщиком Microsoft OLE DB для SQL Server. Приложения, использующие ADO, OLE DB или API-интерфейсы, основанные на ODBC, могут использовать этот формат отметок времени ODBC для представления даты и времени. Escape-последовательности меток времени ODBC имеют следующий формат: { literal_type ‘constant_value’ }: — literal_type определяет тип escape-последовательности. Метки времени имеют три описателя literal_type:1) d = только дата2) t = только время3) ts = метка времени (время + дата) — ‘constant_value’ является значением escape-последовательности. Значение constant_value должно соответствовать этим форматам для каждого literal_type.d : гггг-мм-ддt : чч:мм:ссts : гггг-мм-дд чч:мм:сс

Problems related to SQL convert date operations

When you try to convert to date it is not always possible. The following example shows a common error:

1 2	declare@vardatevarchar(100)=’11242016′ selectCONVERT(datetime,@vardate)asdataconverted

The error message is the following:

Msg 242, Level 16, State 3, Line 22

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

You need separators for the date like a “/”, a “.” or a “-“.

The following example, modifies the string from 11242016 to 11-24-2016 and then converts to sql date:

1 2 3 4 5

declare@vardatevarchar(100)=’11242016′ set@vardate=SUBSTRING(@vardate,1,2)+’-‘+SUBSTRING(@vardate,3,2)+’-‘+SUBSTRING(@vardate,5,4) selectCONVERT(date,@vardate)asdataconverted

We use substring to concatenate the “-” to use an acceptable date format and then we use the CONVERT function to convert the characters to sql date.

Операторы

Для применения MS SQL дата необходимо разбираться в операторах:

Функции:

DATE_FORMAT() — для разных способов вывода сведений о времени.

Указывает из чего состоит:

DATE() — из даты со временем.

Возврат значений:

• DATEDIFF() — временного отрезка между определенными датами.
• EXTRACT() —  единого значения даты/времени.

Выполнения действий:

• DATE_ADD() — добавления определенного количества единиц времени до выборки;
• DATE_SUB() — вычисления указанного интервала от даты.

Популярные операции с ms sql дата

Рассмотрим наиболее популярные операции, осуществляемые с датами (Dates) и временем (Time):

Функция	Выполняет действия	Примечание
ADDDATE (date, INTERVAL value)	Процесс сложения даты (date) и определенного значения (value)	Value – любая единица измерения времени (в годах, кварталах, месяцах, неделях, днях, часах, минутах, секундах)
SUBDATE (date, INTERVAL value)	Процесс вычитания из даты date значения value
PERIOD_ADD (period, n)	Сложение значения period с n-ным количеством месяцев
TIMESTAMPADD (interval, n, date)	Сложение даты date с n-ным временным интервалом interval	Interval – те же единицы измерения, что и для value в ADDDATE (включая микросекунды)
SUBTIME (date, time).	Вычитание из даты date определенного временного интервала time

Функции подсчета интервала

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

Функция	Выполняет действия	Примечание
TIMEDIFF (date1, date2)	Определяет разницу между date1 и date2	Результат представлен в часах, минутах, секундах
DATEDIFF (date1, date2)	Высчитывает разницу между date1 и date2	Результат выдает в днях. Она поможет подсчитать число дней от любой даты
PERIOD_DIFF (period1, period2)	Находит разницу между периодами period1 и period2	Выражается в месяцах. Необходимо, чтобы параметр период имел дату в формате YYYYMM
TIMESTAMPDIFF (interval, date1, date2)	Находит разницу между date1 и date2	Выражается в тех единицах, что и interval, который соответствует аналогичному interval  в TIMESTAMPADD

Функции вычисления разных значений в SQL Server и иной полезной информации

В SQL Server существуют специальные функции, с помощью которых можно найти много полезной информации. Часть их представлена в таблице:

Функция	Возвращает значение	Примечание
DATE (datetime)	Текущей (Current) даты	Missing Time
TIME (datetime)	Времени	Missing Date
TIMESTAMP (date)	Полного значения даты вместе со временем
DAY (date)/ DAYOFMONTH (date)	Порядкового номера определенного дня в месяце	Являются синонимами, поэтому можно использовать любую
DAYNAME (date)	Наименования дня недели
DAYOFWEEK (date)	Выражение дня в неделе в цифрах	Отсчет начинается в воскресенье (1) и заканчивается в субботу (7)
WEEKDAY (date)	Цифрового значения дня в неделе	Отсчет начинается в понедельник (0) и заканчивается в воскресенье (6)
WEEK (date)	Цифрового значения недели в году	Воскресенье – первый день недели
WEEKOFYEAR (datetime)	Цифрового значения недели в году	Понедельник – первый день недели
MONTH (date)	Цифрового значения месяца в году
MONTHNAME (date)	Наименования месяца
QUARTER (date)	Цифрового значения квартала в году
YEAR (date)	Года	1000 – 9999
DAYOFYEAR (date)	Порядкового номера дня в календарном году
HOUR (datetime)	Часа
MINUTE (datetime)	Минут
SECOND (datetime)	Секунд
EXTRACT (type FROM date)	Части даты date, которая определена параметром type
TO_DAYS (date)	Даты, преобразованной в число дней, которые прошли с нулевого года
FROM_DAYS (n)	Число дней, которые прошли с нулевого года	Эта и предыдущая ф-ция взаимообратны
UNIX_TIMESTAMP (date)	Перевода даты в секунды, которые прошли с 01.01.1970г.
FROM_UNIXTIME (n)	Число секунд, которые прошли с 01.01.1970г., переведенных в дату	Эта и предыдущая ф-ция взаимообратны
TIME_TO_SEC (time)	Перевода времени в число секунд, которые прошли с начала суток
SEC_TO_TIME (n)	Числа секунд, которые прошли с начала суток, конвертированные в привычный формат времени	Эта и предыдущая ф-ция взаимообратны
MAKEDATE (year, n)	Даты, полученной путем преобразования года year и порядкового номера n дня в году

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

Если тип данных аргумента date не содержит указанной части datepart, функция вернет значение по умолчанию для этой части datepart, только если для date указан литерал.

Например, значение «год-месяц-день» по умолчанию для любого типа данных date равно 1900-01-01. Приведенная ниже инструкция содержит аргументы компонентов даты для datepart, аргумент времени для date и возвращает .

Если аргумент date указан как переменная или столбец таблицы и тип данных этой переменной или столбца не содержит указанной части datepart, функция возвращает ошибку 9810. В этом примере переменная @t имеет тип данных time. Этот пример завершается ошибкой, потому что год даты не является допустимым для типа данных time:

SQL Работа с датами

Вы можете легко сравнить две даты, если нет никакого компонента времени!

Предположим, что у нас есть следующая таблица «Orders»:

OrderId	ProductName	OrderDate
1	Geitost	2008-11-11
2	Camembert Pierrot	2008-11-09
3	Mozzarella di Giovanni	2008-11-11
4	Mascarpone Fabioli	2008-10-29

Теперь мы хотим выбрать записи с порядковым номером «2008-11-11» из приведенной выше таблицы.

Мы используем следующую инструкцию SELECT:

SELECT * FROM Orders WHERE OrderDate=’2008-11-11′

Результирующий набор будет выглядеть следующим образом:

OrderId	ProductName	OrderDate
1	Geitost	2008-11-11
3	Mozzarella di Giovanni	2008-11-11

Теперь предположим, что таблица «Orders» выглядит следующим образом (обратите внимание на компонент time в столбце «OrderDate»):

OrderId	ProductName	OrderDate
1	Geitost	2008-11-11 13:23:44
2	Camembert Pierrot	2008-11-09 15:45:21
3	Mozzarella di Giovanni	2008-11-11 11:12:01
4	Mascarpone Fabioli	2008-10-29 14:56:59

Если мы используем тот же оператор SELECT, что и выше:

SELECT * FROM Orders WHERE OrderDate=’2008-11-11′

Мы не получим никакого результата! Это происходит потому, что запрос ищет только даты без временной части.

Совет: Чтобы ваши запросы были простыми и удобными в обслуживании, не допускайте компонентов времени в ваших датах!

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

Каждое выражение datepart и его краткие формы возвращают одно и то же значение.

Возвращаемое значение зависит от языка среды, задаваемого инструкцией SET LANGUAGE, и от параметра конфигурации сервера «язык по умолчанию» для имени входа. Если значение date является строковым литералом некоторого формата, то возвращаемое значение зависит от функции SET DATEFORMAT. Инструкция SET DATEFORMAT не изменяет возвращаемое значение, если дата представляется выражением столбца типа данных даты или времени.

Ниже представлена таблица соответствия аргументов функции datepart и значений, возвращенных выражением . Аргумент date имеет тип данных datetimeoffset(7) . Последние две позиции значения, возвращаемого функцией nanosecond datepart, всегда . Масштаб этого значения составляет 9.

.123456700

datepart	Возвращаемое значение
year, yyyy, yy	2007 г.
quarter, qq, q	4
month, mm, m	10
dayofyear, dy, y	303
day, dd, d	30
week, wk, ww	44
weekday, dw	3
hour, hh	12
minute, n	15
second, ss, s	32
millisecond, ms	123
microsecond, mcs	123456
nanosecond, ns	123456700
tzoffset, tz	310
iso_week, isowk, isoww	44

SQL Учебник

SQL ГлавнаяSQL ВведениеSQL СинтаксисSQL SELECTSQL SELECT DISTINCTSQL WHERESQL AND, OR, NOTSQL ORDER BYSQL INSERT INTOSQL Значение NullSQL Инструкция UPDATESQL Инструкция DELETESQL SELECT TOPSQL MIN() и MAX()SQL COUNT(), AVG() и …SQL Оператор LIKESQL ПодстановочныйSQL Оператор INSQL Оператор BETWEENSQL ПсевдонимыSQL JOINSQL JOIN ВнутриSQL JOIN СлеваSQL JOIN СправаSQL JOIN ПолноеSQL JOIN СамSQL Оператор UNIONSQL GROUP BYSQL HAVINGSQL Оператор ExistsSQL Операторы Any, AllSQL SELECT INTOSQL INSERT INTO SELECTSQL Инструкция CASESQL Функции NULLSQL ХранимаяSQL Комментарии

2 ответа

В этом ответе предполагается, что вы используете Oracle, как указано в вашем вопросе.

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

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

Если ваш столбец является строкой:

Если это число:

GMB
14 Июн 2020 в 09:32

(Читайте ниже мой ответ, если это столбец)

Предполагая, что это текстовая строка:

Предполагая, что является столбцом строки (символ, текст и т. Д.), А не / / / , затем используйте { {X5}} функция с . Значение соответствует ISO 8601, поскольку значения расположены в порядке, даже если в них нет тире.

На этой странице есть ссылка на номера: https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql -server — ver15

Предполагая, что это фактический столбец:

Быстрый и грязный способ — преобразовать в и затем использовать тот же код, что и выше, как если бы это было текстовое поле, но не делайте этого потому что это медленно

Предполагая, что это фактический столбец (лучший ответ):

Нам нужно будет использовать и извлекать каждый компонент с использованием арифметики Base-10 ( fun )!

Если у нас есть целое число, представляющее отформатированную дату _(ужас) , например , тогда:

• Мы можем получить день, выполнив (например, )
• Мы можем получить месяц, сначала разделив на 100, чтобы удалить часть дня, а затем : (например, , )
• Мы можем получить год, разделив , (например, ).

Btw :

• SQL Server использует для оператора Modulo вместо .
• Целочисленное деление вызывает усечение, а не получение десятичных значений или значений с плавающей запятой (например, , а не ).

Вот так:

Очевидно, что иметь два вложенных подзапроса — трудная задача для работы с _{(SQL обладает ужасной эргономикой, я не понимаю, как или почему SQL не позволяет выражениям в предложении использоваться другими выражениями в тот же запрос — это действительно плохая эргономика …)} — но мы можем преобразовать его в скалярный UDF (и SQL Server встроит скалярные пользовательские функции, поэтому влияние на производительность не будет).

В этой функции есть блок , так как существует вероятность обработки недопустимого значения, такого как (которое не является реальной датой, поскольку в 2020 году нет 99-го месяца с 0-м днем) ) . В этом случае функция возвращает .

Что мы можем использовать в запросе так:

Поскольку не может обмениваться результатами выражений с другими выражениями в том же запросе, вам все равно придется использовать внешний запрос для работы с (или повторить вызов функции ):

2

Dai
14 Июн 2020 в 01:40

About Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases.

He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams.

He also helps with translating SQLShack articles to Spanish

View all posts by Daniel Calbimonte

2.22.1. Преобразование дат

Для начала посмотрим, как можно преобразовывать дату, раз уж в предыдущем разделе мы затронули эту тему.

В приложении 1, в описании типа datetime сказано, что SQL сервер использует формат даты, при котором вначале идет месяц (мм/дд/гггг). Если все оставить так, как есть, то пользователям будет неудобно вводить данные в таблицы, ведь мы привыкли начинать ввод даты с числа, а потом уже указывать месяц.

Для преобразования дат лучше всего использовать функцию CONVERT. Самый простейший пример преобразования с помощью этой функции:

SELECT convert(datetime, '31.1.2005' ,103)

Как мы уже знаем, после указания функции convert, в скобках указывается новый тип и переменная. Но у нас здесь три параметра. Что это за третий параметр. Это стиль, в котором представлена дата. Если посмотреть на дату ‘31.1.2005’, то видно, что вначале явно идет число, ведь 31-го месяца не существует. Если бы команда выглядела так: SELECT convert(datetime, ‘31.1.2005’), то сервер вернул бы нам ошибку, потому что не смог бы преобразовать дату.

Последний параметр как раз указывает номер стиля, в котором мы представили дату и благодаря ему сервер правильно прочитает параметр ‘31.1.2005’. Стили вы можете увидеть в таблице 2.1. Это наиболее часто используемые стили. Более полный вариант таблицы можно увидеть в файле помощи. В первой колонке показан номер стиля, если год показан в сокращенном виде (то есть без указания века, например 05). Во второй колонке номер стиля, если год указан полностью (например, 2005).

Таблица 2.1. Стили преобразования типа данных даты

Сокращенный год	Полный год	Формат даты
—	0 или 100	Месяц дд гггг чч:ммAM (или PM)
1	101	мм/дд/гггг
2	102	гг.мм.дд
3	103	дд/мм/гггг
4	104	дд.мм.гггг
5	105	дд-мм-гггг
6	106	дд месяц гггг
7	107	месяц дд гггг
8	108	чч:мм:сс
10	110	мм-дд-гггг
11	111	гг/мм/дд
12	112	Ггммдд

Исходя из этой таблицы, стиль 103 соответствует принятому в России формату дд/мм/гггг и именно его мы использовали.

В следующем запросе мы используем преобразование даты в операторе INSERT для вставки данных в таблицу:

INSERT INTO tbPeoples
 (vcFamil, vcName, vcSurname, idPosition, dDateBirthDay) 
VALUES('СОДОРОЧКИН', 'ИВАН', 'СЕРГЕЕВИЧ', 11, 
  convert(datetime, '31.1.2005' ,103))

Есть еще один способ указания формата даты – с помощью оператора SET. Этот оператор мы будем достаточно подробно рассматривать в главе 4.1, но то, что касается преобразования даты мы рассмотрим сейчас. Посмотрите на следующий пример:

SET DATEFORMAT dmy
GO
DECLARE @vdate datetime
SET @vdate = '29/11/00'
SELECT @vdate

В первой строке указывается формат необходимой даты с помощью оператора SET DATEFORMAT. После этого указывается формат в виде букв d, m и y, которые отражают необходимую нам последовательность в указании даты. В данном случае мы говорим, что мы будем указывать в дате сначала число, потом месяц и в конце будет идти год.

Теперь объявляется переменная vdate, которой присваивается дата в заданном формате и эта дата выводиться на экран.

Необходимо отметить, что один раз выполнив команду SET, установленное значение действует в течение всей сессии. Но если отключиться и подключиться к серверу заново, сервер снова будет требовать ввода даты в формате по умолчанию.

Example

The first example will be simple, we have a varchar column with a date in a table, but we need to convert the varchar to date. We need to do a SQL convert date.

Here it is script to create the table with data:

1 2 3 4 5 6 7 8 9 10 11 12

CREATETABLEdbo.delivers( productidtinyintNOTNULL, datenvarchar(100)NULL, CONSTRAINTPK_deliversPRIMARYKEYCLUSTERED ( productidASC )WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ONPRIMARY )ONPRIMARY GO INSERTdbo.delivers(productid,date)VALUES(1,N’02-03-2005′) INSERTdbo.delivers(productid,date)VALUES(2,N’03-05-2006′) INSERTdbo.delivers(productid,date)VALUES(3,N’04-05-2011′)

We want to convert the column date from nvarchar(100) to a date.

To do it, we are going to try to modify the design of the table:

We will try to change the Data Type to smalldatetime:

You will receive the following error message:

Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.

To solve this error, in SSMS go to Tools > Options menu:

In Options, go to Designers >Table and Database Designers and uncheck the Prevent saving changes that require table re-creation:

This option will disable to option to prevent saving table recreation. When you modify the column data type, it requires table re-creation.

Now, you can save the design and your table will be converted to date and the SQL convert date is completed:

Возвращаемые значения дат с типом данных smalldatetime и частью даты в виде секунд или долей секунды.

Значение секунд даты типа smalldatetime всегда равно 00. Для значения date типа smalldatetime действуют указанные ниже условия.

• Для части даты datepart секунды second и значения number в диапазоне от –30 до +29 не вносит никаких изменений.
• Для части даты datepart секунды second и значения number меньше –30 или больше +29 выполняет добавление, начиная с одной минуты.
• Для части даты datepart миллисекунды millisecond и значения number в диапазоне от –30 001 до + 29 998 не вносит никаких изменений.
• Для части даты datepart миллисекунды millisecond и значения number меньше –30 001 или больше +29 998 выполняет добавление, начиная с одной минуты.

Recommended Reading

Continue your learning on Microsoft SQL Server dates with these tips and
tutorials:

• Determine SQL Server Date and Time Parts with DATEPART and DATENAME
  Functions

• SQL Server Date and Time Data Types

• SQL Server function to convert integer date to datetime format

• SQL Database DateTime Best Practices

• Format SQL Server Dates with FORMAT Function

• SQL
  Server Date Functions

• Add and Subtract Dates using DATEADD in SQL Server

Next Steps

• The formats listed above are not inclusive of all formats provided. Experiment
  with the different format numbers to see what others are available.
• These formats can be used for all date/time functions, as well as data being
  served to clients, so experiment with these data format conversions to see if
  they can provide data more efficiently.

• Also, check out the

  SQL Server FORMAT Function to Format Dates.

Related Articles

SQL Server 2008 Date and Time Data Types

SQL Server function to convert integer date to datetime format

Determine SQL Server Date and Time Parts with DATEPART and DATENAME Functions

Format SQL Server Dates with FORMAT Function

SQL Server function to convert integer date to datetime format

Determine SQL Server Date and Time Parts with DATEPART and DATENAME Functions

Add and Subtract Dates using DATEADD in SQL Server

Format SQL Server Dates with FORMAT Function

Handle conversion between time zones in SQL Server — part 1

Creating a date dimension or calendar table in SQL Server

SQL Server DateTime Best Practices

Create an Extended Date Dimension for a SQL Server Data Warehouse

Using T-SQL to find events that overlap (or don’t) in SQL Server

SQL Server Date and Time Functions with Examples

Simplify Date Period Calculations in SQL Server

Fill In Missing Dates for SQL Server Query Output

SQL Server FORMAT Function for Dates, Numbers and SQL Server Agent Dates

Update only Year, Month or Day in a SQL Server Date

SQL Convert Date to YYYYMMDD

How to Expand a Range of Dates into Rows using a SQL Server Numbers Table

Popular Articles

Format SQL Server Dates with FORMAT Function

SQL Server Cursor Example

SQL Server CROSS APPLY and OUTER APPLY

Rolling up multiple rows into a single row and column for SQL Server data

SQL Server DROP TABLE IF EXISTS Examples

How to tell what SQL Server versions you are running

Add and Subtract Dates using DATEADD in SQL Server

Using MERGE in SQL Server to insert, update and delete at the same time

SQL Server Loop through Table Rows without Cursor

Resolving could not open a connection to SQL Server errors

SQL NOT IN Operator

SQL Convert Date to YYYYMMDD

Concatenate SQL Server Columns into a String with CONCAT()

SQL Server Row Count for all Tables in a Database

Ways to compare and find differences for SQL Server tables and data

How to Get Current Date in SQL Server

Execute Dynamic SQL commands in SQL Server

SQL Server Database Stuck in Restoring State

Searching and finding a string value in all columns in a SQL Server table

About the author

Edgewood Solutions is a technology company focused on Microsoft SQL Server and founder of MSSQLTips.com.
View all my tips

Описание типа данных datetime2

Property (Свойство)	Значение
Синтаксис	datetime2
Использование	DECLARE @MyDatetime2 datetime2(7) CREATE TABLE Таблица1 ( Столбец1 datetime2(7) )
Формат строковых литералов по умолчанию (используется для клиента нижнего уровня)	ГГГГ-ММ-ДД чч:мм:сс Дополнительные сведения см. в подразделе «Обратная совместимость для клиентов низкого уровня» следующего раздела.
Диапазон даты	От 0001-01-01 до 31.12.99 С 1 января 1 года нашей эры до 31 декабря 9999 года нашей эры
Диапазон времени	от 00:00:00 до 23:59:59.9999999
Диапазон смещения часового пояса	None
Диапазоны элементов	ГГГГ представляет собой четырехзначное число от 0001 до 9999, определяющее год. ММ — двузначное число от 01 до 12, представляющее месяц указанного года. Обозначение ДД состоит из двух цифр, представляющих день указанного месяца, и принимает значения от 01 до 31 в зависимости от месяца. Обозначение «чч» состоит из двух цифр, представляющих час, и принимает значения от 00 до 23. Обозначение «мм» состоит из двух цифр, представляющих минуту, и принимает значения от 00 до 59. Обозначение «сс» состоит из двух цифр, представляющих секунду, и принимает значения от 00 до 59. Обозначение n* может содержать от нуля до семи цифр, представляющих доли секунды, и принимает значения от 0 до 9999999. В Informatica доли секунды усекаются при n > 3.
Длина в символах	Минимальная — 19 позиций (ГГГГ-ММ-ДД чч:мм:сс), максимальная — 27 позиций ((ГГГГ-ММ-ДД чч:мм:сс.0000000)
Точность, масштаб	От 0 до 7 цифр, с точностью 100 нс. Точность по умолчанию составляет 7 цифр.
Объем памяти 1	6 байтов для представления точности меньше 3 цифр.7 байтов — для точности в 3 или 4 цифры.Для представления любых других значений точности требуется 8 байт 2.
Точность	100 наносекунд
Значение по умолчанию	1900-01-01 00:00:00
Календарь	Григорианский
Определяемая пользователем точность в долях секунды	Да
Учет и сохранение смещения часового пояса	Нет
Учет перехода на летнее время	Нет

1 Указанные значения относятся к несжатым rowstore. Использование сжатия данных или columnstore может изменить размер хранилища для каждого уровня точности. Кроме того, размер хранилища на диске и в памяти может различаться. Например, значения datetime2 при использовании пакетного режима всегда требует 8 байт в памяти.

2 При приведении значения datetime2 к значению varbinary к значению varbinary добавляется дополнительный байт для сохранения точности.

Сведения о метаданных типа данных см. в статье sys.systypes (Transact-SQL) или TYPEPROPERTY (Transact-SQL). В некоторых типах данных дат и времени точность и масштаб разные. Сведения о получении точности и масштаба для столбца см. в статье COLUMNPROPERTY (Transact-SQL), COL_LENGTH (Transact-SQL) или sys.columns (Transact-SQL).

Функция DATEPART

DATEPART ( datepart , date )

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

Datepart	Допустимые сокращения
Weekday — день недели	dw

DATEPARTSET DATEFIRST

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

Пример

SELECT pt.trip_no, DATEADD(mi, DATEPART(hh,time_out)*60 + DATEPART(mi,time_out), date) 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