Sqlhints.com

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.

MS SQL user function, procedures – Пользовательские функции и процедуры MS SQL.

Очень часто бывает необходимо в аналитических процедурах в MS SQL Server’e “вытащить” первое число месяца. Например, в отчетах месяц можно отображать как первое число этого месяца, что весьма удобно с точки зрения сортировки данных в сводной таблице MS Excel.

В t-SQL можно поступить таким образом:

Здесь с помощью встроенной функции dateadd от переменной @d вычитается количество дней равное дате в исходном месяце (получается последняя дата предыдущего месяца) и прибавляется один день, чтобы получить первое число исходного месяца. Для того, чтобы избавиться от “лишнего” времени применяем функцию convert, где преобразуем переменную @d к типу date (только дата без времени).

Можно даже написать функцию, которая принимает в качестве параметра дату

Пример использования функции:

ОБЛАСТЬ ПРИМЕНЕНИЯ: SQL Server База данных SQL Azure Azure Synapse Analytics (хранилище данных SQL) Parallel Data Warehouse APPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse

Эта функция возвращает последний день месяца, содержащего указанную дату, с необязательным смещением. This function returns the last day of the month containing a specified date, with an optional offset.

Синтаксические обозначения в Transact-SQL Transact-SQL Syntax Conventions

Использование Power Query.

Надстройка Excel Power Query может помочь вам перевести дату в месяц и извлекать его номер или название.

Итак, у нас есть список дат и нужно вытащить из них месяц.

• Прежде всего, преобразуйте ваши данные в таблицу.
• После этого выберите любую из ячеек в таблице и перейдите на вкладку «Данные».
• Нажмите кнопку «Из таблицы».

Ваша таблица будет загружена в Power Query.

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

• Кликните правой кнопкой мыши заголовок столбца.
• Нажмите «Создать дубликат».

• Кликните по заголовку нужного столбца правой кнопкой мыши.
• Перейдите Преобразование ➜ Месяц ➜ Название месяца.

Ваши даты тут же будут преобразованы, как на скриншоте ниже.

• Еще одна вещь, которую имеет смысл сделать: щелкните правой кнопкой мыши заголовок столбца и переименуйте его в «Месяц».
• Теперь нажмите «Закрыть и загрузить», и ваши данные будут перенесены в рабочий лист.

Плюсы —

Ваши исходные данные ни при каком раскладе не пострадают.

Минусы —

У вас должен быть установлен Power Query в вашей версии Excel. Название месяца будет полным, в именительном падеже.

Вот основные способы, как можно в Excel вывести месяц из даты. Надеюсь, эти советы были вам полезны.

Вычисление интервала между двумя значениями DATE

Интервал между двумя значениями из семейства вычисляется простым вычитанием одного значения из другого. Результат всегда относится к типу Например: 

Результат:

Интервалы могут быть отрицательными или положительными. Отрицательный интервал означает, что более недавняя дата вычитается из даты, находящейся в прошлом: 

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

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

Результат:

Три дня — понятно, но что такое 0.40208333333333333333333333333333333333? Даты часто усекаются перед вычитанием или же усекается полученный результат. Использование типов и значительно упрощает правильное усечение длинной дробной строки до часов, минут и секунд.

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

Функция работает по следующим правилам:

• Если наступает позже , возвращает положительное число.
• Если наступает раньше , возвращает отрицательное число.
• Если и относятся к одному месяцу, функция возвращает дробное значение из диапазона от –1 до +1.
• Если и приходятся на последние дни соответствующих месяцев, функция возвращает целое число (без дробного компонента).
• Если и относятся к разным месяцам и хотя бы одна из них не приходится на последний день месяца, функция возвращает дробное значение. (Дробный компонент вычисляется для месяцев, состоящих из 31 дня, с учетом разницы компонентов времени двух дат.)

Несколько примеров использования : 

Результаты:

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

В соответствии с этим правилом количество месяцев между 31 января 1994 года и 28 февраля 1994 года равно 1 — удобное целое число. Однако количество месяцев между 31 января 1994 года и 1 марта 1994 года увеличивается на .032258065. Как и в случае с вычитанием , при работе с часто используется функция .

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

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 Комментарии

SQL References

SQL Keywords
ADD
ADD CONSTRAINT
ALL
ALTER
ALTER COLUMN
ALTER TABLE
AND
ANY
AS
ASC
BACKUP DATABASE
BETWEEN
CASE
CHECK
COLUMN
CONSTRAINT
CREATE
CREATE DATABASE
CREATE INDEX
CREATE OR REPLACE VIEW
CREATE TABLE
CREATE PROCEDURE
CREATE UNIQUE INDEX
CREATE VIEW
DATABASE
DEFAULT
DELETE
DESC
DISTINCT
DROP
DROP COLUMN
DROP CONSTRAINT
DROP DATABASE
DROP DEFAULT
DROP INDEX
DROP TABLE
DROP VIEW
EXEC
EXISTS
FOREIGN KEY
FROM
FULL OUTER JOIN
GROUP BY
HAVING
IN
INDEX
INNER JOIN
INSERT INTO
INSERT INTO SELECT
IS NULL
IS NOT NULL
JOIN
LEFT JOIN
LIKE
LIMIT
NOT
NOT NULL
OR
ORDER BY
OUTER JOIN
PRIMARY KEY
PROCEDURE
RIGHT JOIN
ROWNUM
SELECT
SELECT DISTINCT
SELECT INTO
SELECT TOP
SET
TABLE
TOP
TRUNCATE TABLE
UNION
UNION ALL
UNIQUE
UPDATE
VALUES
VIEW
WHERE

MySQL Functions
String Functions:
ASCII
CHAR_LENGTH
CHARACTER_LENGTH
CONCAT
CONCAT_WS
FIELD
FIND_IN_SET
FORMAT
INSERT
INSTR
LCASE
LEFT
LENGTH
LOCATE
LOWER
LPAD
LTRIM
MID
POSITION
REPEAT
REPLACE
REVERSE
RIGHT
RPAD
RTRIM
SPACE
STRCMP
SUBSTR
SUBSTRING
SUBSTRING_INDEX
TRIM
UCASE
UPPER

Numeric Functions:
ABS
ACOS
ASIN
ATAN
ATAN2
AVG
CEIL
CEILING
COS
COT
COUNT
DEGREES
DIV
EXP
FLOOR
GREATEST
LEAST
LN
LOG
LOG10
LOG2
MAX
MIN
MOD
PI
POW
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
SUM
TAN
TRUNCATE

Date Functions:
ADDDATE
ADDTIME
CURDATE
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURTIME
DATE
DATEDIFF
DATE_ADD
DATE_FORMAT
DATE_SUB
DAY
DAYNAME
DAYOFMONTH
DAYOFWEEK
DAYOFYEAR
EXTRACT
FROM_DAYS
HOUR
LAST_DAY
LOCALTIME
LOCALTIMESTAMP
MAKEDATE
MAKETIME
MICROSECOND
MINUTE
MONTH
MONTHNAME
NOW
PERIOD_ADD
PERIOD_DIFF
QUARTER
SECOND
SEC_TO_TIME
STR_TO_DATE
SUBDATE
SUBTIME
SYSDATE
TIME
TIME_FORMAT
TIME_TO_SEC
TIMEDIFF
TIMESTAMP
TO_DAYS
WEEK
WEEKDAY
WEEKOFYEAR
YEAR
YEARWEEK

Advanced Functions:
BIN
BINARY
CASE
CAST
COALESCE
CONNECTION_ID
CONV
CONVERT
CURRENT_USER
DATABASE
IF
IFNULL
ISNULL
LAST_INSERT_ID
NULLIF
SESSION_USER
SYSTEM_USER
USER
VERSION

SQL Server Functions
String Functions:
ASCII
CHAR
CHARINDEX
CONCAT
Concat with +
CONCAT_WS
DATALENGTH
DIFFERENCE
FORMAT
LEFT
LEN
LOWER
LTRIM
NCHAR
PATINDEX
QUOTENAME
REPLACE
REPLICATE
REVERSE
RIGHT
RTRIM
SOUNDEX
SPACE
STR
STUFF
SUBSTRING
TRANSLATE
TRIM
UNICODE
UPPER

Numeric Functions:
ABS
ACOS
ASIN
ATAN
ATN2
AVG
CEILING
COUNT
COS
COT
DEGREES
EXP
FLOOR
LOG
LOG10
MAX
MIN
PI
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
SQUARE
SUM
TAN

Date Functions:
CURRENT_TIMESTAMP
DATEADD
DATEDIFF
DATEFROMPARTS
DATENAME
DATEPART
DAY
GETDATE
GETUTCDATE
ISDATE
MONTH
SYSDATETIME
YEAR

Advanced Functions
CAST
COALESCE
CONVERT
CURRENT_USER
IIF
ISNULL
ISNUMERIC
NULLIF
SESSION_USER
SESSIONPROPERTY
SYSTEM_USER
USER_NAME

MS Access Functions
String Functions:
Asc
Chr
Concat with &
CurDir
Format
InStr
InstrRev
LCase
Left
Len
LTrim
Mid
Replace
Right
RTrim
Space
Split
Str
StrComp
StrConv
StrReverse
Trim
UCase

Numeric Functions:
Abs
Atn
Avg
Cos
Count
Exp
Fix
Format
Int
Max
Min
Randomize
Rnd
Round
Sgn
Sqr
Sum
Val

Date Functions:
Date
DateAdd
DateDiff
DatePart
DateSerial
DateValue
Day
Format
Hour
Minute
Month
MonthName
Now
Second
Time
TimeSerial
TimeValue
Weekday
WeekdayName
Year

Other Functions:
CurrentUser
Environ
IsDate
IsNull
IsNumeric

SQL Quick Ref

Remarks Remarks

Функция GETDATE может использоваться в инструкциях Transact-SQL Transact-SQL везде, где допустимо использование выражения datetime. Transact-SQL Transact-SQL statements can refer to GETDATE anywhere they can refer to a datetime expression.

GETDATE является недетерминированной функцией. GETDATE is a nondeterministic function. Невозможно проиндексировать представления и выражения, ссылающиеся на эту функцию в столбце. Views and expressions that reference this function in a column cannot be indexed.

Использование SWITCHOFFSET с функцией GETDATE() может вызвать замедление выполнения запроса, поскольку оптимизатор запросов не может получить точные оценки количества элементов для значения GETDATE. Using SWITCHOFFSET with the function GETDATE() can cause the query to run slowly because the query optimizer is unable to obtain accurate cardinality estimates for the GETDATE value. Рекомендуется заранее вычислить значение GETDATE, а затем указать это значение в запросе, как показано в следующем примере. We recommend that you precompute the GETDATE value and then specify that value in the query as shown in the following example. Кроме того, с помощью указания запроса OPTION (RECOMPILE) можно вынудить оптимизатор запросов повторно компилировать план запроса при каждом выполнении одного и того же запроса. In addition, use the OPTION (RECOMPILE) query hint to force the query optimizer to recompile a query plan the next time the same query is executed. В этом случае оптимизатор будет иметь точные оценки количества элементов для GETDATE(), что позволит ему сформировать более эффективный план запроса. The optimizer will then have accurate cardinality estimates for GETDATE() and will produce a more efficient query plan.

Как определить номер месяца по дате в Excel

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

1. Функция МЕСЯЦ() – определить номер месяца из даты.

Это наиболее очевидный и простой способ. Например:

• =МЕСЯЦ(A2) – возвращает месяц из даты в ячейке A2.
• =МЕСЯЦ(ДАТА(2020,7,15)) – возвращает число 7, соответствующее июлю.
• =МЕСЯЦ(«15-Апр-2020») – очевидно, возвращает число.

2. Функция ТЕКСТ() – вывести месяц в виде текстовой строки.

Альтернативный способ получить номер месяца из даты Excel — использовать функцию ТЕКСТ:

• =ТЕКСТ(A2; «м») — возвращает номер без нуля в начале, от 1 до12.
• =ТЕКСТ(A2;»мм») — возвращает номер всегда из двух цифр, от 01 до 12.

Будьте очень внимательны при использовании текстовых функций, потому что они всегда возвращают порядковые номера месяцев в виде текстовых значений. И если вы планируете выполнить какие-то дальнейшие вычисления или использовать полученные числа в других формулах, вам лучше придерживаться функции МЕСЯЦ().

Или же при помощи несложного математического действия преобразуйте текст в число:

=—ТЕКСТ(A2; «м») 

На следующем скриншоте показаны результаты всех приведенных выше формул. Обратите внимание на выравнивание по правому краю чисел, возвращаемых функцией МЕСЯЦ() (ячейки C2 и C3), в отличие от выравнивания по левому краю текстовых значений, возвращаемых функциями ТЕКСТ (ячейки C5 и C6)

Операторы

Для применения 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 дня в году

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

Каждое выражение datepart и его краткие формы возвращают одно и то же значение. Each datepart and its abbreviations return the same value.

Возвращаемое значение зависит от языка среды, задаваемого инструкцией SET LANGUAGE, и от параметра конфигурации сервера «язык по умолчанию» для имени входа. The return value depends on the language environment set by using SET LANGUAGE, and by the Configure the default language Server Configuration Option of the login. Если значение date является строковым литералом некоторого формата, то возвращаемое значение зависит от функции SET DATEFORMAT. The return value depends on SET DATEFORMAT if date is a string literal of some formats. Инструкция SET DATEFORMAT не изменяет возвращаемое значение, если дата представляется выражением столбца типа данных даты или времени. SET DATEFORMAT does not change the return value when the date is a column expression of a date or time data type.

Ниже представлена таблица соответствия аргументов функции datepart и значений, возвращенных выражением SELECT DATEPART(datepart,’2007-10-30 12:15:32.1234567 +05:10′) . This table lists all datepart arguments, with corresponding return values, for the statement SELECT DATEPART(datepart,’2007-10-30 12:15:32.1234567 +05:10′) . Аргумент date имеет тип данных datetimeoffset(7) . The date argument has a datetimeoffset(7) data type. Последние две позиции значения, возвращаемого функцией nanosecond datepart, всегда 00 . Масштаб этого значения составляет 9. The last two positions of the nanosecond datepart return value are always 00 and this value has a scale of 9:

.123456700 .123456700

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

SQL Working with Dates

Look at the following table:

Orders Table

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

Now we want to select the records with an OrderDate of «2008-11-11» from the table above.

We use the following statement:

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

The result-set will look like this:

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

Note: Two dates can easily be compared if there is no time component involved!

Now, assume that the «Orders» table looks like this (notice the
added time-component in the «OrderDate» column):

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

If we use the same statement as above:

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

we will get no result! This is because the query is looking only for dates with no time portion.

Tip: To keep your queries simple and easy to maintain, do not use time-components in your dates, unless you have to!

❮ Previous
Next ❯

Поддерживаемые форматы строковых литералов для типа данных 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 : гггг-мм-дд чч:мм:сс

The FORMAT() Function

We can use the function to return the same values as with the function, and more.

This is a more versatile function than the previous ones. It allows you to format date/time as well as numeric values as strings. The return value is either nvarchar or null (depending on the input), and the length of the string is determined by the specified format.

also provides more options for how the datepart is presented. You can specify whether to display it as a single character, two characters, three, four, and even five in some cases.

Here are examples to demonstrate.

Day

DECLARE @date datetime2 = '2018-06-02 08:24:14.3112042';
SELECT 
    FORMAT(@date, 'd ') AS d,
    FORMAT(@date, 'dd') AS dd,
    FORMAT(@date, 'ddd') AS ddd,
    FORMAT(@date, 'dddd') AS dddd;

Result:

+-----+------+-------+----------+
| d   | dd   | ddd   | dddd     |
|-----+------+-------+----------|
| 2   | 02   | Sat   | Saturday |
+-----+------+-------+----------+

Month

DECLARE @date datetime2 = '2018-06-02 08:24:14.3112042';
SELECT 
    FORMAT(@date, 'M ') AS M,
    FORMAT(@date, 'MM') AS MM,
    FORMAT(@date, 'MMM') AS MMM,
    FORMAT(@date, 'MMMMM') AS MMMM;

Result:

+-----+------+-------+--------+
| M   | MM   | MMM   | MMMM   |
|-----+------+-------+--------|
| 6   | 06   | Jun   | June   |
+-----+------+-------+--------+

Year

DECLARE @date datetime2 = '2018-06-02 08:24:14.3112042';
SELECT     
    FORMAT(@date, 'y ') AS y,
    FORMAT(@date, 'yy') AS yy,
    FORMAT(@date, 'yyy') AS yyy,
    FORMAT(@date, 'yyyy') AS yyyy,
    FORMAT(@date, 'yyyyy') AS yyyyy;

Result:

+-----+------+-------+--------+---------+
| y   | yy   | yyy   | yyyy   | yyyyy   |
|-----+------+-------+--------+---------|
| 18  | 18   | 2018  | 2018   | 02018   |
+-----+------+-------+--------+---------+

Notice that we have the option of formatting the year portion as five digits.

About the Number of Digits

When returning a datepart in digit form, the format specifier determines the minimum number of digits to return. For example, when using  , the year will be returned as three digits if the year is but as four digits if the year is .

Example:

DECLARE @date datetime2 = '0008-06-02 08:24:14.3112042';
SELECT     
    FORMAT(@date, 'y ') AS y,
    FORMAT(@date, 'yy') AS yy,
    FORMAT(@date, 'yyy') AS yyy,
    FORMAT(@date, 'yyyy') AS yyyy,
    FORMAT(@date, 'yyyyy') AS yyyyy;  

Result:

+-----+------+-------+--------+---------+
| y   | yy   | yyy   | yyyy   | yyyyy   |
|-----+------+-------+--------+---------|
| 8   | 08   | 008   | 0008   | 00008   |
+-----+------+-------+--------+---------+

Also, when using the single option (e.g. ) as the date specifier, you’ll need to add a space if you only want that datepart returned by itself. If you don’t do this, you’ll get more than that single datepart.

Example:

DECLARE @date datetime2 = '2008-06-02 08:24:14.3112042';
SELECT 
    FORMAT(@date, 'd ') AS 'Space',
    FORMAT(@date, 'd') AS 'No Space',
    FORMAT(@date, 'M ') AS 'Space',
    FORMAT(@date, 'M') AS 'No Space',
    FORMAT(@date, 'y ') AS 'Space',
    FORMAT(@date, 'y') AS 'No Space';

Result:

+---------+------------+---------+------------+---------+------------+
| Space   | No Space   | Space   | No Space   | Space   | No Space   |
|---------+------------+---------+------------+---------+------------|
| 2       | 6/2/2008   | 6       | June 2     | 8       | June 2008  |
+---------+------------+---------+------------+---------+------------+

For more  examples, see How to Format the Date and Time in SQL Server.

Возвращаемые значения дат с типом данных 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 выполняет добавление, начиная с одной минуты.

Функция текущей даты SQL, её возможности

Функция текущей даты CURDATE() возвращает значение текущей даты в формате ‘YYYY-MM-DD’ и ‘YYYYDDMM’. Вычисляя несколькими способами (их как раз и разберём в этом параграфе) разницу значений дат, можно определить такие важные значения, как возраст человека, его трудовой стаж, продолжительность различных процессов и явлений и многое другое.

В примерах работаем с базой данных «Театр». Таблица Play содержит данные о постановках. Таблица Team — о ролях актёров. Таблица Actor — об актёрах. Таблица Director — о режиссёрах. Поля таблиц, первичные и внешние ключи можно увидеть на рисунке ниже (для увеличения нажать левой кнопкой мыши).

Это уже база с большим объёмом данных по сравнению с примерами ко многим другим темам нашего курса. Поэтому не будем приводить строки данных таблиц и таблицы результатов запросов. Однако это будет компенсировано подробным разбором логики построения запросов, которые, надо признать, имеют достаточно высокую сложность.

Пример 1. Сформировать список актеров старше 70 лет. Пишем следующий запрос:

В этом запросе вычисляется разница между текущей датой CURDATE() и датой рождения актёра BirthDate, содержащейся в таблице ACTOR. Для вычисления разницы применена функция TIMESTAMPDIFF(). Ключевое слово YEAR — задаёт единицу измерения — в годах интервала между датами. Вычисленное значение и результат его сравнения с числом 70 вполне пригодны в качестве условия выборки в секции WHERE. Следует учесть, что функция TIMESTAMPDIFF() существует лишь в MySQL. В других диалектах SQL для этого есть функция DATEDIFF, а для задания единицы измерения применяются различные ключевые слова в различных вариантах написания.

Для вычисления разницы дат можно использовать и оператор «минус». Это сделано в следующем примере.

Пример 2. Вывести список актеров, которые не задействованы в новых постановках (в постановках последних 3 лет). Использовать CURDATE(), NOT IN. Запрос будет следующим:

В этом запросе задействована функция YEAR(). Аргументами её являются дата премьеры постановки premieredate из таблицы play и текущая дата. Для вычисления разницы использован оператор «минус». Он имеет больший приоритет по сравнению с оператором сравнения и поэтому выполняется первым. Вычисленнное выражение сравнивается с числом 3 и подходящие значения служат условием выборки из таблицы play, содержащей данные о постановках. Этот подзапрос — самый глубокий в «матрёшке» целого запроса. Подзапрос более высокого уровня из таблицы team, содержащей данные о ролях, выбирает идентификаторы актёров, с помощью предиката IN. Выбираются те актёры, которые участвовали в постановках трёх послежних лет. И, наконец, самый внешний запрос к таблице actor выбирает значения с отрицанием (NOT IN) значения предыдущего подзапроса.

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

Пример 3. Сформировать список актеров с их стажем (в днях). Использовать CURDATE(), GROUP BY. Запрос будет следующим:

В этом запросе разница между текущей датой CURDATE() и датой премьеры постановки PremiereDate из таблицы Play вычисляется как имя столбца в результирующей таблице. Поскольку эти даты имеют один и тот же формат, для вычисления разницы достаточно использовать оператор «минус». Разница вычислена. Но из таблицы Play невозможно напрямую «достучаться» до таблицы Actor, содержащей данные об актёрах. Поэтому используем соединение (JOIN) этой таблицы с таблицей Team, которая уже связана с таблицей Actor при помощи ключа Actor_ID. Соединение таблиц Team и Actor — второе в этой цепочке из трёх таблиц.

Составить SQL запросы с текущей датой самостоятельно, а затем посмотреть решения

Пример 4. Определить самого востребованного актера за последние 5 лет. Оператор JOIN использовать 2 раза. Использовать CURDATE(), LIMIT 1.

Пример 5. Определить спектакли, в которых средний возраст актеров от 20 до 30 (использовать BETWEEN, GROUP BY, AVG).