MS SQL Server: Формат даты

     Для вывода даты в формате DD.MM.YYYY

SomeDate = CONVERT(VarChar(50), getdate(), 104)

   Выведется текущая дата в указанном формате. Формат указывается цифрами, в данном случае 104. 

      Остальные значения:

SELECT CONVERT(varchar, getdate(), 2)    — ‘yy.mm.dd’
SELECT CONVERT(varchar, getdate(), 3)    — ‘dd/mm/yy’
SELECT CONVERT(varchar, getdate(), 4)    — ‘dd.mm.yy’
SELECT CONVERT(varchar, getdate(), 5)    — ‘dd-mm-yy’
SELECT CONVERT(varchar, getdate(), 6)    — ‘dd mmm yy’
SELECT CONVERT(VarChar, GETDATE(), 101)  — ‘mm/dd/yyyy’
SELECT CONVERT(VarChar, GETDATE(), 102)  — ‘yyyy.mm.dd’
SELECT CONVERT(VarChar, GETDATE(), 103)  — ‘dd/mm/yyyy’
SELECT CONVERT(VarChar, GETDATE(), 104)  — ‘dd.mm.yyyy’
SELECT CONVERT(varchar, GETDATE(), 105)  — ‘dd-mm-yyy’
SELECT CONVERT(varchar, GETDATE(), 111)  — ‘yyyy/mm/dd’
SELECT CONVERT(varchar, GETDATE(), 112)  — ‘yyyymmdd’

SELECT CONVERT(varchar,GETDATE(),108) ‘hh:mm:ss’

SELECT CONVERT(varchar,GETDATE(),114) ‘hh:mm:ss:mmm’
SELECT CONVERT(varchar,GETDATE(),113) ‘dd Mmm yyyy hh:mm:ss:mmm’
SELECT CONVERT(VarChar(50), GETDATE(), 100) ‘mmm dd yyyy mm:ss AM’

 
     и еще: 

SELECT CONVERT(varchar,GETDATE(),0)
Return Value = Jun 6 2007 11:07PM
SELECT CONVERT(varchar,GETDATE(),100)
Return Value = Jun 6 2007 11:07PM

SELECT CONVERT(varchar,GETDATE(),1)
Return Value = 06/06/07
SELECT CONVERT(varchar,GETDATE(),101)
Return Value = 06/06/2007

SELECT CONVERT(varchar,GETDATE(),2)
Return Value = 07. 06.06
SELECT CONVERT(varchar,GETDATE(),102)
Return Value = 2007.06.06

SELECT CONVERT(varchar,GETDATE(),3)

Return Value = 06/06/07
SELECT CONVERT(varchar,GETDATE(),103)
Return Value = 06/06/2007

SELECT CONVERT(varchar,GETDATE(),4)
Return Value = 06.06.07
SELECT CONVERT(varchar,GETDATE(),104)
Return Value = 06.06.2007

SELECT CONVERT(varchar,GETDATE(),5)
Return Value = 06-06-07
SELECT CONVERT(varchar,GETDATE(),105)
Return Value = 06-06-2007

SELECT CONVERT(varchar,GETDATE(),6)
Return Value = 06 Jun 07
SELECT CONVERT(varchar,GETDATE(),106)
Return Value = 06 Jun 2007

SELECT CONVERT(varchar,GETDATE(),7)
Return Value = Jun 06, 07
SELECT CONVERT(varchar,GETDATE(),107)
Return Value = Jun 06, 2007

SELECT CONVERT(varchar,GETDATE(),8)

Return Value = 23:38:49
SELECT CONVERT(varchar,GETDATE(),108)
Return Value = 23:38:49

SELECT CONVERT(varchar,GETDATE(),9)
Return Value = Jun 6 2007 11:39:17:060PM
SELECT CONVERT(varchar,GETDATE(),109)
Return Value = Jun 6 2007 11:39:17:060PM

SELECT CONVERT(varchar,GETDATE(),10)
Return Value = 06-06-07
SELECT CONVERT(varchar,GETDATE(),110)
Return Value = 06-06-2007

SELECT CONVERT(varchar,GETDATE(),11)
Return Value = 07/06/06
SELECT CONVERT(varchar,GETDATE(),111)
Return Value = 2007/06/06

SELECT CONVERT(varchar,GETDATE(),12)
Return Value = 070606
SELECT CONVERT(varchar,GETDATE(),112)

Return Value = 20070606

SELECT CONVERT(varchar,GETDATE(),13)
Return Value = 06 Jun 2007 23:40:14:577
SELECT CONVERT(varchar,GETDATE(),113)
Return Value = 06 Jun 2007 23:40:14:577

SELECT CONVERT(varchar,GETDATE(),14)
Return Value = 23:40:29:717
SELECT CONVERT(varchar,GETDATE(),114)
Return Value = 23:40:29:717

SELECT CONVERT(varchar,GETDATE(),20)
Return Value = 2007-06-06 23:40:51
SELECT CONVERT(varchar,GETDATE(),120)
Return Value = 2007-06-06 23:40:51

SELECT CONVERT(varchar,GETDATE(),21)
Return Value = 2007-06-06 23:41:10. 153
SELECT CONVERT(varchar,GETDATE(),121)

Return Value = 2007-06-06 23:41:10.153

SELECT CONVERT(varchar,GETDATE(),126)
Return Value = 2007-06-06T23:41:10.153

SELECT CONVERT(varchar,GETDATE(),131)
Return Value = 21/05/1428 11:41:10:153PM

 

Функция EXTRACT — части даты или даты-времени

Функция EXTRACT извлекает отдельные части из даты или даты-времени.

Синтаксис

SELECT EXTRACT(что_извлечь FROM дата) FROM имя_таблицы WHERE условие

Вместо 'что_извлечь' можно написать, к примеру, DAY — тогда из даты будет извлечен день, или, к примеру, YEAR — тогда будет извлечен год. Если же я напишу так: YEAR_MONTH — то будет извлечен год и месяц (слитно, без разделителя). Если вам нужно извлекать несколько частей не слитно, а используя разделитель — используйте

DATE_FORMAT.

Форматы вывода

  • SECOND секунды
  • MINUTE минуты
  • HOUR часы
  • DAY дни
  • MONTH месяцы
  • YEAR года
  • MINUTE_SECOND минуты и секунды
  • HOUR_MINUTE часы и минуты
  • DAY_HOUR дни и часы
  • YEAR_MONTH года и месяцы
  • HOUR_SECOND часы, минуты, секунды
  • DAY_MINUTE дни, часы, минуты
  • DAY_SECOND дни, часы, минуты, секунды

Таблицы для примеров

таблица users
id
айди
name
имя		date
дата рождения
1user11988-03-01
2user21989-04-02
3user31990-05-03

Пример

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

SELECT *, EXTRACT(DAY FROM date) as day FROM users

Результат выполнения кода:

id
айди		name
имя		date
дата рождения		day
день
1user11988-03-011
2user21989-04-022
3user31990-05-033

Пример

В данном примере при выборке из таблицы из даты извлекается год:

SELECT *, EXTRACT(YEAR FROM date) as year FROM users

Результат выполнения кода:

id
айди		name
имя		date
дата рождения		year
год
1user11988-03-011988
2user21989-04-021989
3		user31990-05-031990

Пример

В данном примере при выборке из таблицы из даты извлекается год и месяц (слитно):

SELECT *, EXTRACT(YEAR_MONTH FROM date) as yearmonth FROM users

Результат выполнения кода:

id
айди		name
имя		date
дата рождения		yearmonth
год и месяц
1user11988-03-01198803
2user21989-04-02198904
3user31990-05-03199005

Смотрите также

  • функцию DATE,
    которая извлекает дату из даты-времени
  • функцию YEAR,
    которая извлекает год из даты
  • функцию MONTH,
    которая извлекает месяц из даты
  • функцию DAY,
    которая извлекает день из даты

SQL | Даты | Codecademy

Анонимный участник

Анонимный участник3077 всего публикаций

Анонимный участник

Опубликовано 4 августа 2021 г. • Обновлено 19 октября 2021 г.

Добавить в Документы

Значок мини-стрелки вправо

Даты в SQL часто записываются в следующем формате:

  • ДАТА : ГГГГ-ММ-ДД
  • ДАТАВРЕМЯ : ГГГГ-ММ-ДД чч:мм:сс

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

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

order_id товар цена количество дата_заказа
1 Пончик 2,49 2 2022-08-16 08:04:23
2 Печенье 0,99 3 16.08.2022 09:43:00
3 Пончик 2,49 1 2022-08-16 11:25:12
4 Сэндвич с яйцом 7,99 1 17. 08.2022 11:45:41
5 Кофе со льдом 3,99 2 2022-08-17 12:18:50

 
 
 ВЫБЕРИТЕ ВРЕМЯ(дата_заказа) 
 
 
 ИЗ винного погреба;

Это вернет только время с столбец order_date .

ВРЕМЯ(дата_заказа)
08:04:23
09:43:00
11:25:12
11:45:41
12:18:50

Даты

ДАТА()
Преобразует метки времени в даты (ГГГГ-ММ-ДД).

DATEADD()
Возвращает интервал даты/времени, добавленный к указанной дате. Доступны версии для SQL Server и MySQL.

DATEDIFF()
Вычисляет и возвращает разницу между двумя значениями даты. Доступно в SQL Server и MySQL.

DATETIME()
Возвращает дату и время (ГГГГ-ММ-ДД чч:мм:сс).

STRFTIME()
Возвращает отформатированную дату.

TIME()
Преобразует метки времени во время (чч:мм:сс).

Все участники

Хотите внести свой вклад?

  • Узнайте больше о том, как принять участие.
  • Отредактируйте эту страницу на GitHub, чтобы исправить ошибку или внести улучшения.
  • Отправьте отзыв, чтобы сообщить нам, как мы можем улучшить Документы.

Изучение SQL на Codecademy

Путь навыков

Анализ данных с помощью SQL

Научитесь анализировать данные с помощью SQL и подготовьтесь к техническим собеседованиям.

Checker Dense Включает

8 курсов

Checker DenseCertificate Icon С сертификатом

Checker DenseLevel Icon Новичок 902 31 Дружелюбный

15 Уроки

Бесплатный курс

Изучение SQL

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

Checker DenseLevel Icon Новичок Дружелюбный

4 Уроки

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

  • Группировка результатов по периоду времени
    • Пример: сколько человек каждую неделю создавали учетную запись?
  • Сравните итоги недели с неделей
    • Пример: как изменились заказы по сравнению с прошлой неделей?
  • Найти продолжительность между двумя датами
    • Пример: сколько дней прошло между созданием клиентом учетной записи и размещением первого заказа?
  • Дальнейшее чтение

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

Сценарий Пример
Группировка результатов по периоду времени Сколько человек создавало учетную запись каждую неделю?
Сравнение недельных итогов Каково количество заказов на этой неделе по сравнению с прошлой неделей?
Найти продолжительность между двумя датами Сколько дней прошло между созданием клиентом учетной записи и размещением первого заказа?

Группировка результатов по периоду времени

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

Пример: сколько человек каждую неделю создавали учетную запись?

Здесь мы хотим вернуть два столбца:

 | НЕДЕЛЯ | СОЗДАННЫЕ АККАУНТЫ |
|------|----|
| . .. | ... |

Давайте посмотрим на нашу таблицу People . Мы можем SELECT * FROM people LIMIT 1 , чтобы увидеть список полей, или просто щелкнуть значок книги , чтобы увидеть метаданные о таблицах в базе данных, с которой мы работаем.

Рис. 1 . Используйте боковую панель Data Reference для поиска информации о таблицах.

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

Нам нужно сгруппировать эти создания учетных записей, но вместо того, чтобы группировать их по дате, нам нужно сгруппировать их по неделям. Чтобы увидеть, на какую неделю приходится каждая дата created_at , мы будем использовать функцию DATE_TRUNC .

DATE_TRUNC позволяет округлить («обрезать») временные метки до нужной вам степени детализации: неделя, месяц и т. д. DATE_TRUNC принимает два аргумента: текст и отметку времени и возвращает отметку времени. Этот первый текстовый аргумент — это период времени, в данном случае «неделя», но мы могли бы указать другую степень детализации, например, месяц, квартал или год (проверьте документацию вашей базы данных на 9).0016 DATE_TRUNC , чтобы увидеть варианты). Для наших целей здесь мы напишем DATE_TRUNC('week', created_at) , что будет возвращать дату понедельника каждой недели. Кстати, SQL нечувствителен к регистру, так что вы можете регистрировать свой код как хотите ( date_trunc тоже работает или DaTe_TrUnc , если вы спрашиваете саркастически).

Мы также будем использовать псевдонимы для результатов, чтобы дать столбцам более конкретные имена. Например, используя ключевое слово AS , мы изменим Count(*) для отображения как account_created .

 ВЫБЕРИТЕ
  DATE_TRUNC('неделя', created_at) КАК неделя,
  COUNT(*) AS account_created
ОТ
  люди
ГРУППА ПО
  неделя
СОРТИРОВАТЬ ПО
  неделя

Что возвращает:

 | НЕДЕЛЯ | АККАУНТЫ_СОЗДАН |
|---------|------------------|
| 18. 04.16 | 13 |
| 25.04.16 | 17 |
| 02.05.16 | 17 |
| ... | ... |

Мы можем визуализировать этот результат как линейный график :

Рис. 2 . Линейная диаграмма, показывающая количество учетных записей, созданных за неделю.

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

Сравнение недельных итогов

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

Пример: как изменились заказы по сравнению с прошлой неделей?

Здесь мы ищем неделю, количество заказов за эту неделю и изменение по неделям (количество заказов увеличилось, уменьшилось или осталось прежним?):

 | НЕДЕЛЯ | COUNT_OF_ORDERS | WOW_CHANGE |
|---------|-----------------|-------------|
| ... | ... | ... |

Чтобы получить эти данные, нам сначала нужно получить таблицу, в которой указано количество заказов в неделю. Мы сделаем в основном то же самое, что только что сделали для таблицы People , но на этот раз для таблицы Orders : мы будем использовать DATE_TRUNC для группировки количества заказов по неделям.

 ВЫБЕРИТЕ
  DATE_TRUNC('неделя', orders.created_at) КАК неделя,
  COUNT(*) КАК order_count
ОТ
  заказы
ГРУППА ПО
  неделя

Что дает нам:

 | НЕДЕЛЯ | ORDER_COUNT |
|----------|--------------|
| 01.07.2019 | 115 |
| 02.07.2018 | 119 |
| 03.07.2017 | 78 |
| ... | ... |

Мы будем использовать эти результаты для построения остальной части запроса. Теперь нам нужно взять количество заказов за каждую неделю (которое мы будем называть w1 ) и вычесть его из количества за предыдущую неделю (которое мы назовем w2 ). Проблема здесь в том, что для выполнения вычитания нам нужно каким-то образом получить счет каждой недели в той же строке, что и счет предыдущей недели.

Вот как мы это сделаем:

  • Оберните наши результаты в Common Table Expression (CTE).
  • Присоединить этот CTE к самому себе, сдвинув присоединение на 1 неделю
  • Вычтите общее количество заказов за предыдущую неделю из общего количества за каждую неделю, чтобы получить недельное изменение

Мы сделаем запрос над Common Table Expression (CTE), используя ключевое слово WITH . По сути, CTE — это способ присвоить переменную промежуточным результатам, с которыми мы затем можем обращаться так, как если бы результаты были реальной таблицей в базе данных (например, 9).0016 Заказы или Таблица ). Мы назовем таблицу результатов order_count_by_week . Затем воспользуемся этой таблицей и соединим ее саму с собой, но со смещением: ее строки сдвинуты на одну неделю.

Вот запрос со смещением:

 С order_count_by_week КАК (
  ВЫБИРАТЬ
    DATE_TRUNC('неделя', orders.created_at) КАК неделя,
    COUNT(*) КАК order_count
  ОТ
    заказы
  ГРУППА ПО
    неделя
)
ВЫБИРАТЬ
  *
ОТ
  order_count_by_week w1
  LEFT JOIN order_count_by_week w2 ON w1. week = DATEADD(WEEK, 1, w2.week)
СОРТИРОВАТЬ ПО
  w1.неделя

Этот запрос дает:

 | НЕДЕЛЯ | ORDER_COUNT | НЕДЕЛЯ | ORDER_COUNT |
|-----------|--------------|-----------|-------------|
| 25.04.2016 | 1 | | |
| 02.05.2016 | 3 | 25.04.2016 | 1 |
| 09.05.2016 | 3 | 02.05.2016 | 3 |
| ... | ... | ... | ... |

Давайте разберемся, что здесь происходит. Мы назвали CTE order_count_by_week как w1 , а затем снова как w2 . Затем мы соединили эти два CTE слева. Ключевым здесь является функция DATEADD , которую мы использовали для добавления недели к каждому значению w2.week для смещения объединенных столбцов:

 LEFT JOIN order_count_by_week w2 ON w1.week = DATEADD(WEEK, 1, w2.week)

Функция DATEADD принимает период времени (WEEK), количество этих недель для применения (в данном случае 1, так как мы хотим узнать разницу по сравнению с предыдущей неделей) и столбец даты, к которому применяется добавление ( w2. week ). (Обратите внимание, что некоторые базы данных используют INTERVAL вместо DATEADD , например w2.week + INTERVAL '1 неделя' ). Это «выравнивает» строки, но с отклонением на одну неделю (обратите внимание на отсутствие значений во второй группе количества недель/порядков для этой первой строки выше).

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

  • Неделя размещения заказов
  • Количество заказов на эту неделю
  • Изменение по неделям (т. е. разница между подсчетами на этой неделе и на предыдущей неделе).

Вот полный запрос:

 С order_count_by_week КАК (
  ВЫБИРАТЬ
    DATE_TRUNC('неделя', orders.created_at) КАК неделя,
    COUNT(*) КАК order_count
  ОТ
    заказы
  ГРУППА ПО
    неделя
)
ВЫБИРАТЬ
  w1. неделя,
  w1.order_count КАК count_of_orders,
  w1.order_count - w2.order_count КАК wow_change
ОТ
  order_count_by_week w1
  LEFT JOIN order_count_by_week w2 ON w1.week = DATEADD(WEEK, 1, w2.week)
СОРТИРОВАТЬ ПО
  w1.неделя

Что возвращает:

 | НЕДЕЛЯ | COUNT_OF_ORDERS | WOW_CHANGE |
|---------|-----------------|-------------|
| 25.04.16 | 1 | |
| 02.05.16 | 3 | 2 |
| 09.05.16 | 3 | 0 |
| ... | ... | ... |

Найти продолжительность между двумя датами

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

Пример: сколько дней прошло между созданием клиентом учетной записи и размещением первого заказа?

Чтобы ответить на это, давайте вернем четыре столбца:

  • Идентификатор клиента
  • Дата создания клиентом учетной записи
  • Дата, когда клиент разместил свой первый заказ
  • Разница между этими двумя датами

Теперь, чтобы получить эту информацию, нам нужно получить данные из таблиц People и Orders . Но мы не хотим объединять эти две таблицы, так как нам нужен только первый заказ, размещенный каждым клиентом.

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

 ВЫБЕРИТЕ
  ID пользователя,
  MIN(создано_в) как first_order_date
ОТ
  заказы
ГРУППА ПО
  ID пользователя

Здесь мы группируем заказы по клиентам ( GROUP BY user_id ) и используем функцию MIN , чтобы найти самую раннюю дату заказа. Мы сохраним эти результаты как first_orders и продолжим наш запрос.

 С first_orders КАК (
  ВЫБИРАТЬ
    ID пользователя,
    MIN(создано_в) как first_order_date
  ОТ
    заказы
  ГРУППА ПО
    ID пользователя
)
ВЫБИРАТЬ
  люди.идентификатор,
  people.created_at AS account_creation,
  first_orders.first_order_date,
  РАЗНИЦА ДАТЫ(
    'день', people.created_at, first_orders.first_order_date
  ) КАК days_before_first_order
ОТ
  ЛЮДИ
  ПРИСОЕДИНЯЙТЕСЬ к first_orders ON first_orders.user_id = people. id
СОРТИРОВАТЬ ПО
  создание аккаунта

Что дает нам:

 | ID | АККАУНТ_СОЗДАНИЕ | FIRST_ORDER_DATE | DAYS_BEFORE_FIRST_ORDER |
|--------------------|------------------|--------------------------------|------------|
| 915 | 19.04.16 21:35 | 9.10.16 8:42 | 173 |
| 1712 | 21.04.16 23:46 | 15.08.16 4:01 | 116 |
| 2379 | 22.04.16 4:07 | 22.05.16 3:56 | 30 |
| ... | ... | ... | ... |

Подводя итог: мы отобрали у клиента created_at date и присоединил запрос к нашему CTE. Мы использовали функцию DATEDIFF , чтобы найти количество дней между созданием учетной записи и их первым заказом, а затем сохранили результат как days_before_first_order . DATEDIFF принимает период времени (например, «день», «неделя», «месяц») и возвращает количество периодов между двумя временными метками.

(Учитывая, что образец базы данных является случайным, наши ответы не очень хорошо соответствуют действительности — как часто люди ждут 173 дня между созданием учетной записи и покупкой?)

Дальнейшее чтение

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

No related posts.