Главная SQL, Без рубрики, Новое Окончательный справочник по типам данных datetime
  • Окончательный справочник по типам данных datetime

    time_management_3 Цель этой статьи заключается в разъяснении особенностей работы с типами DATETIME в SQL Server, в том числе часто встречающихся заблуждений и общих рекомендаций по их преодолению. Благодаря Frank Kalis эта статья переведена на немецкий язык.

    Благодарности:

    Я хочу поблагодарить следующих людей за их ценные предложения и материалы для этой статьи: Steve Kass, Aaron Bertrand, Jacco Schalkwijk, Klaus Oberdalhoff, Hugo Kornelis, Dan Guzman и Erland Sommarskog.

    Версии SQL Server

    Данная статья применима к SQL Server 7.0, 2000, 2005 и 2008, если не указано иначе.

    Типы даты и времени в SQL Server

    До SQL Server 2008 в SQL Server имелось два типа данных для обработки даты и времени. Поскольку в этой статье мы очень часто будем ссылаться на эти типы, введем сокращенную аббревиатуру для каждого типа в двух представленных ниже таблицах (колонка СК):

    Название

    СК

    Мин. значение

    Макс. значение

    Точность

    Используемая память

    smalldatetime sdt 1900-01-01 00:00:00 2079-06-06 23:59:00 минута 4 байта
    datetime dt 1753-01-01 00:00:00.000 9999-12-31 23:59:59.997 3.33 мс 8 байт

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

    Если задавать только ту часть что касается даты, то SQL Server сохранит время в виде 00:00:00.000.
    А если устанавливать значение только времени, SQL Server будет хранить дату как 01.01.1900.
    Это очень важно. Прочтите снова.

    SELECT CAST(‘20041223’ AS datetime)

    ———————–
    2004-12-23 00:00:00.000

    SELECT CAST(’14:23:58′ AS datetime)

    ———————–
    1900-01-01 14:23:58.000

    С появлением SQL Server 2008 было получено несколько новых типов данных связанных со значениями даты и времени:

    Название

    СК

    Мин. значение

    Макс. значение

    Точность

    Используемая память

    datetime2 dt2 0001-01-01 00:00:00.0000000 9999-12-31 23:59:59.9999999 100 нс 6-8 байт
    date d 0001-01-01 9999-12-31 день 3 байта
    time t 00:00:00.0000000 23:59:59.9999999 100 нс 3-5 байта
    datetimeoffset dto 0001-01-01 00:00:00.0000000 9999-12-31 23:59:59.9999999 100 нс 8-10 байт
    • Как вы можете видеть, мы наконец-то получили типы данных только для даты (date) и только для времени (time).
    • Datetime2 это “лучшее DATETIME” по ряду причин, и занимает не многим больше памяти, чем datetime и потенциально даже меньше!
    • Для нового типа включающего величину времени, вы можете задать “точность до дробных секунд” определяя используемый порядок цифр в секундах после запятой. Так, time(3) может хранить значения наподобие 14:23:12.567, которые при вводе значения 14:23:12:5677 округляются до 14:23:12:568.
    • Новый тип datetimeoffset содержит в себе часть смещения местного часового пояса.

    Форматы даты и времени

    Распространенное заблуждение – то, что SQL Server хранит эти типы данных в некотором специфическом удобном для чтения формате. Это не так. SQL Server хранит такие значения в своем внутреннем формате (например, два целых числа для datetime и smalldatetime). При этом, когда используется T-SQL для установки значения (например, в выражении INSERT) вы выражаете его как текстовую строку. Существуют также правила для интерпретации SQL Server-ом различных форматов даты-строки. Но заметим, что в любом случае SQL Server не запомнит этот формат.

    Входные форматы для даты и времени

    Есть много доступных форматов для приведения значений к виду date/time/datetime. Некоторые из них “лучше”, чем другие, и далее вы поймете почему “лучше”. Примечательно, что все эти форматы применимы для всех типов. Так даже формат “только время” применимо для типа “только дата” и т.д . (В статье игнорируется часть смещения местного часового пояса, которая используется только в типе данных datetimeoffset – более подробно о котором в Books Online.)

    Название

    СК

    Формат

    SET DATEFORMAT зависимость

    SET LANGUAGE зависимость

    Нейтральность к языку

    Unseparated u ‘19980223 14:23:05’ нет нет для всех
    Separated s ’02/23/1998 14:23:05′ для всех для всех нет
    ANSI SQL ansisql ‘1998-12-23 14:23:05’ sdt, dt sdt, dt не для sdt и dt
    Alphabetic a ’23 February 1998 14:23:05′ нет для всех (название месяца) нет
    ODBC datetime odt {ts ‘1998-02-23 14:23:05’} нет нет для всех
    ODBC date od {d ‘1998-02-23’} нет нет для всех
    ODBC time ot {t ’14:23:05′} нет нет для всех
    ISO 8601 iso ‘1998-02-23T14:23:05’ нет нет для всех
    Time t ’14:23:05′
    ‘2:23:05 PM’
    нет нет для всех
    • Отметим, ANSI SQL действительно только частный случай формата с разделительными символами Separated (так называемый “цифровой”), использующий в качестве разделителей тире (-), косую черту (/) и точку (.). Но, поскольку это единственный формат, определенный в стандарте ANSI SQL, по мнению автора это стоит упомянуть как частный случай.
    • Большинство форматов позволяют удалять часть содержащую дату и/или время, и в некоторых случаях это может выглядеть немного … странно. Казалось бы, неразумно указывать, например ‘2008-08-25’ как тип время (time), но в конечном результате это аналогично не установке значений в строке datetime. Рассмотрим ниже:
      SELECT CAST(AS time)
      SELECT CAST(‘2008-08-25’ AS time)


      Оба запроса выдают один и тот же результат (time 00:00:00).
    • ODBC форматы (ODBC datetime, ODBC date, ODBC time) отличаются в том смысле, что у них есть маркер (literal_type, t, d или ts), который необходимо правильно задать в зависимости от того, получать ли дату и время, только дату или только время.
    • Для применения формата ISO 8601 необходим сегмент даты и времени.
    • SET DATEFORMAT наследует свои настройки от SET LANGUAGE (но явно заданный SET DATEFORMAT аннулирует более поздний SET LANGUAGE). Языковые настройки по умолчанию задаются для каждого языка используемого при вводе логина. Язык по умолчанию для логина задается при помощи sp_configure.
    • Правила, относительно форматирования части даты и новых типов могут привести к путанице. Microsoft стремится, чтобы дата новых соответствующих типов данных (date, datetime2 и datetimeoffset) была менее зависима от настроек и еще более соответствовала требованиям ANSI SQL. И в результате – новые типы нейтрально-языковые для выделения составляющих даты-времени до тех пор пока год на первом месте. SQL Server-у необходимо определить, что эта часть является годом, и поэтому требуется 4 позиции составляющих год (yyyy, а не yy). Если это так, то строка будет интерпретироваться как сначала год, затем месяц и, наконец, день – независимо от DATEFORMAT или языковых установок. Если же вначале указывается месяц, тогда DATEFORMAT и языковые настройки будут “соблюдаться”:
      SET LANGUAGE British –uses dmy
      GO
      SELECT CAST(’02-23-1998 14:23:05′ AS date) –Error
      GO
      SELECT CAST(‘2/23/1998 14:23:05’ AS date) –Error
      GO
      SELECT CAST(‘1998-02-23 14:23:05’ AS date) –Ok
      GO
      SELECT CAST(‘1998.02.23 14:23:05’ AS date) –Ok
      GO
      SELECT CAST(‘1998/02/23 14:23:05’ AS date) –Ok
      GO
      Первые
      два запроса ошибочны поскольку год не на первой позиции (и нет 23 месяца в 1998 году). В последующих трех запросах ошибок нет, поскольку требования учтены, и год указан первым (и мы используем один из новых стилей типов связанных с датой). Предельно прозрачно, правда? 🙂

    Описание доступных форматов имеется в Books Online, поэтому вдаваться в подробности для каждого формата нет смысла.

    Здесь приведена ссылка на Books Online, смотри подраздел для каждого формата. Отметим, что только формат ODBC time выдаст текущую дату, а не 1900-01-01 как в других форматах типа “только время”.

    Некоторые выводы:

    • Формат Alphabetic использовать не рекомендуется, поскольку SET LANGUAGE влияет на все типы.
    • Не рекомендуется использовать типы smalldatetime и datetime поскольку они зависят не только от SET LANGUAGE, но и от SET DATEFORMAT (в том числе языковые параметры логирования).
    • Вы думаете, что не имеет значения, как комбинация формат/тип влияет (или нет) на SET LANGUAGE, “Я все равно никогда не буду выполнять команду SET LANGUAGE”. Но обратите внимание, предельно осторожно, что значения по умолчанию для DATEFORMAT подтягивается из спецификации логина (CREATE LOGIN т.д.). Представьте ситуацию, когда приложения разработанные в США с логином имеющим по-умолчанию язык(us_english), использующие m/d/y формат и datetime. К примеру, в Германии кто-то начнет использовать это приложение и бац! Приложение не работает. Удалось привлечь ваше внимание? 🙂
    • Типы представленные в SQL Server 2008 являются дружественными, поскольку они не зависят от SET DATEFORMAT, если у вас в Separated формате (с разделением) первым определен год. Для форматов с символами-разделителями (в том числе ANSI SQL), новые типы всегда интерпретируют месяц впереди даты.

    Рекомендации для ввода

    Как вы наверное понимаете, вы не хотите использовать сочетание формата и типа, не зависящих от языка, пока не убедитесь, что у вас есть правильный DATEFORMAT и языковые настройки в своих приложениях. Имейте в виду, что установка этих параметров в хранимых процедурах может привести к перекомпиляции процедуры во время ее выполнения!
    Моя рекомендация заключается в использовании нейтрально-языкового формата. Я обычно использую формат без разделителей Unseparated, главным образом по старой привычке. Формат ISO 8601 имеет преимущество, поскольку определен в качестве международного стандарта.

    SQL Server 2008 был выпущен в момент написания этой статьи, только я вернусь к date, time, datetime2 и возможно к datetimeoffset в следующий раз. В следующий раз, я мог бы использовать формат ANSI SQL (при совместимости ANSI SQL) с новыми типами, но автор предпочел бы, чтобы ANSI SQL поддерживал формат ISO 8601.

    Безопасный путь это использовать или Unseparated, или ISO 8601 в течение ряда лет – даже если работаем с новыми типами данных.

    Предупреждения и распространенные заблуждения

    Позвольте повторить: вы не хотите использовать сочетание формата и типа, нейтрального к языку, пока не убедитесь, что у вас в приложениях правильный DateFormat и языковые настройки.

    Числовой формат может использовать в качестве разделителя тире (-), точку (.) или косую черту (/).
    Правила обработки SQL Server-ом строки, не меняются в зависимости от разделителя. Распространенное заблуждение то, что формат ANSI SQL (одно время неправильно называемый “ISO форматом”), 1998-02-23, нейтрально-языковой для smalldatetime и datetime. Это не так. Это цифровой формат и, следовательно, он зависит от установок SET DATEFORMAT и SET LANGUAGE:

    SET LANGUAGE us_english
    SELECT CAST(‘2003-02-28’ AS datetime)


    ———————–
    2003-02-28 00:00:00.000

    SET LANGUAGE british
    SELECT CAST(‘2003-02-28’ AS datetime)


    Server: Msg 242, Level 16, State 3, Line 4
    The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

    Пожалуйста, прочитайте внимательно сообщение об ошибке. Оно говорит о том, в чем конкретно проблема. Вы определяете значение datetime как строку, и потому что строка не сформирована согласно формату (который Вы используете) и настроек DATEFORMAT, SQL Server не может преобразовать строку в значение datetime.

    Вывод значений DATETIME

    Когда значение datetime выводится SQL Server-ом оно имеет нечитабельный вид. Клиентское приложение форматирует значение так, чтобы оно являлось удобочитаемым для человека. Некоторые среды приложений/разработки будут учитывать региональные настройки на клиентском компьютере и форматировать значения соответственно им. Но это не контролируется SQL Server-ом. Вы можете вернуть данные из SQL Server с помощью специального формата, но для этого необходимо преобразовать значение в строку, и в итоге результат этого преобразования не будет больше типом datetime. Если вы хотите это, используйте функцию CONVERT, у которой есть 3 параметра, которые определяют формат данных при конвертировании из DATETIME в строку. Вот несколько примеров:


    SELECT CONVERT(char(8), CURRENT_TIMESTAMP, 112)——–
    20040312
    SELECT CONVERT(char(19), CURRENT_TIMESTAMP, 120)——————-
    2004-03-12 18:08:43
    SELECT CONVERT(char(10), CURRENT_TIMESTAMP, 103)———-
    12/03/2004

    Поиск для значений DATETIME

    Проблема с datetime-поиском часто состоит в том факте, что если вы используете smalldatetime, datetime, datetime2 или datetimeoffset, которые состоят из таких двух частей как дата и время. Создадим таблицу, которую мы будем использовать в нескольких примерах:

    CREATE TABLE #dts(c1 char(1), dt datetime)
    INSERT INTO #dts (c1, dt) VALUES(‘a’, ‘20040305 09:12:59’)
    INSERT INTO #dts (c1, dt) VALUES(‘b’, ‘20040305 16:03:12’)
    INSERT INTO #dts (c1, dt) VALUES(‘c’, ‘20040306 00:00:00’)
    INSERT INTO #dts (c1, dt) VALUES(‘d’, ‘20040306 02:41:32’)
    INSERT INTO #dts (c1, dt) VALUES(‘e’, ‘20040315 11:45:17’)
    INSERT INTO #dts (c1, dt) VALUES(‘f’, ‘20040412 09:12:59’)
    INSERT INTO #dts (c1, dt) VALUES(‘g’, ‘20040523 11:43:25’)

    Скажем так, мы желаем найти все строки с 2004-03-15. Распространенная ошибка при поиске выглядит так:

    SELECT c1, dt FROM #dts WHERE dt = ‘20040305’

    Нет результатов. Почему? Что случилось? У нас есть два различных типа в предложении WHERE, тип datetime (колонка dt) и строка (справа). SQL Server преобразует один к другому в соответствии с документацией “Приоритетность типов данных” в Books Online. Datetime имеет высокий приоритет, чем строка, так что строка будет первой преобразована в тип datetime. Поскольку мы не указываем часть со временем, строка будет преобразована к значению datetime 2004-03-05 00:00:00. А с таким значением нет ни одной записи.

    Хорошо, следующая попытка. Мы используем функцию CONVERT для табличной колонки dt, и конвертируем ее в строку, чтобы она соответствовала формату строки указанной в правой части:
    SELECT c1, dt FROM #dts WHERE CONVERT(char(8), dt, 112) = ‘20040305’

    Этот запрос вернул ожидаемые строки, но есть одна проблема. Поскольку происходит вычисление колонки dt (с помощью функции CONVERT), SQL Server не может использовать индекс для поддержки этого поиска. Это может иметь катастрофические последствия по производительности! Давайте попробуем BETWEEN:
    SELECT c1, dt FROM #dts WHERE dt BETWEEN ‘20040305’ AND ‘20040306’

    Потому что запрос содержит BETWEEN, вернулись записи по 2004-03-06 00:00:00. Давайте попробуем следующее:
    SELECT c1, dt FROM #dts WHERE dt BETWEEN ‘20040305’ AND ‘20040305 23:59:59.999’

    Что? У нас по-прежнему есть запись по 2004-03-06 00:00:00. Почему? Мы должны посмотреть на определения типов снова. Точность для datetime-типа 3,33 мс. Это значит что 2004-03-05 23:59:59.999 будет округлена до 2004-03-06 00:00:00.000. Не то, что ожидалось. Для того чтобы обойти эту проблему округления, попробуем следующее:

    SELECT c1, dt FROM #dts WHERE dt BETWEEN ‘20040305’ AND ‘20040305 23:59:59.997’

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

    SELECT c1, dt FROM #dts WHERE dt BETWEEN ‘20040305’ AND ‘20040305 23:59:00’

    Наличие двух различных решений в зависимости от используемых типов данных это не то что можно приветствовать. И, кто знает, что произойдет, если Microsoft в будущем добавит типы данных с высокой точностью для сегмента времени (что они и сделали в SQL Server 2008)? Снова вам придется корректировать долю времени в WHERE. Ниже рекомендации автора:

    SELECT c1, dt FROM #dts WHERE dt >= ‘20040305’ AND dt < ‘20040306’

    Мы получаем ожидаемые строки назад и легко понимаем за счет каких критериев. И SQL Server может использовать индекс для колонки dt. Для больших диапазонов этот поиск легко расширить. Скажем так, необходимо отобрать все строки для марта 2004 года:


    SELECT c1, dt FROM #dts WHERE dt >= ‘20040301’ AND dt < ‘20040401’

    Аналогично. Вот еще один способ для поиска всех строк за конкретный месяц:
    SELECT c1, dt FROM #dts WHERE DATEPART(year, dt) = 2004 AND DATENAME(month, dt) = ‘March’

    Выше возвращается ожидаемый результат, но остаются две проблемы. Поскольку в колонке с datetime есть расчеты, SQL Server в запросе не может использовать индекс для колонки dt. И, функция DATENAME зависит от установок SET LANGUAGE.

    Избавляемся от временной части

    Очень часто вам необходимо работать только с датой, и вам необходимо избавиться от временной части. Как вы знаете, сейчас это невозможно, если вы используете тип smalldatetime, datetime, datetime2 или datetimeoffset. Если вы используете тип date, тогда нет проблем – это та же дата, в конце концов! Но если вы настаиваете по какой-то причине использовать тип smalldatetime, datetime, datetime2 или datetimeoffset то, возможно, будет достаточно установки времени в 00:00:00? Вот мое предложение для выражения, возвращающего строку, которая может быть (неявно) преобразована в значение datetime, независимо от SET LANGUAGE или DATEFORMAT:

    CONVERT(char(8), CURRENT_TIMESTAMP, 112)

    Как видите, я использую CONVERT с конверсионным кодом 112, возвращающее дату в виде строки в формате Unseparated. А вы уже знаете, что формат Unseparated при конвертировании к datetime интерпретируется вне зависимости от даты или языковых настроек. Давайте вышеприведенное выражение добавим в CAST для преобразования значения к типу datetime:


    SELECT CAST(CONVERT(char(8), CURRENT_TIMESTAMP, 112) AS datetime)

    В случае, если вы действительно хотите только вернуть в клиентское приложение дату, а не время, тогда вместо этого вам необходимо возвращать строку(или, как в SQL Server 2008, тип date). Еще раз прочтите о различных кодах преобразования для функции CONVERT и вы, вероятно, подберете необходимый Вам формат.

    На эту тему Hugo Kornelis оставил свой отзыв, в котором предлагает иной метод для установки сегмента отвечающий за время в 00:00:00. Эта идея основана на базовой дате (любая дата находится в пределах диапазона datetime SQL Server-а), временной сегмент которой определен как 00:00:00.

    Сначала берем разницу в днях между сегодняшним днем и этой базовой датой. Затем добавляем это количество дней к базовой дате. Алгоритм: [сегодня] – [базовая дата] + [базовая дата], или в T-SQL (с использованием в качестве базовой даты 20040101):
    SELECT DATEADD(day, DATEDIFF(day, ‘20040101’, CURRENT_TIMESTAMP), ‘20040101’)

    Одним из аргументов в пользу использования этого метода является более высокая скорость выполнения по сравнению со строковыми преобразованиями. Ранее автор считал что отличия настолько малы (несколько микросекунд в соответствии с тестами сделанными в 2000 году), так что они даже не измеримы. Однако Dan Guzman сообщил по электронной почте удивительную для меня информацию. Вот цитата из письма:

    “Я обнаружил, что использование метода DATEADD значительно превосходит метод со строкой, когда выражение применяется к столбцу в больших запросах. Например, я часто использую метод DATEADD для группировок по дате, когда колонка datetime включает в себя компоненту время. Недавно я тестировал производительность, но я повторно вспоминаю, что в некоторых случаях это были минуты”.
    Дэну я очень доверяю, и поэтому я думаю, что мне есть чему заново научиться (вначале используя метод DATEADD вместо метода со строкой). Дэн приводит скрипт демонстрирующий это.

    Вот здесь (с некоторыми незначительными изменениями форматирования сделанными мною). Мои тесты показывают достоверные результаты на SQL Server 2005 и 2008, где для 10,000,000 строк метод DATEADD отрабатывает около 6 секунд, а строчный метод отрабатывал около 13 секунд. Дэн сообщает об еще большей разнице на SQL Server 2000.

    2009-04-03, получено письмо от Håkan Borneland. Он отметил, что это обычное явление, для примера в форумах MSDN, используя первый из нижеприведенных вариантов, вместо второй альтернативы (которой автор изначально располагал в своем сценарии тестирования производительности):

    SELECT DATEADD(DAY, 0, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP))
    SELECT DATEADD(DAY, 0, DATEDIFF(DAY, , CURRENT_TIMESTAMP))

    Обратите внимание, что они оба отличаются в версии DATEDIFF, которую мы обсудили несколько выше. Так что теперь у нас есть 4 различных метода достижения этой цели. “Мой” оригинальный метод преобразования строк и три DATEDIFF альтернативы:
    SELECT CAST(CONVERT(CHAR(8), CURRENT_TIMESTAMP, 112) AS datetime) –1
    SELECT DATEADD(DAY, 0, DATEDIFF(DAY, , CURRENT_TIMESTAMP)) –2
    SELECT DATEADD(DAY, DATEDIFF(DAY, ‘20040101’, CURRENT_TIMESTAMP), ‘20040101’) –3
    SELECT DATEADD(DAY, 0, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP)) –4

    Если вы чувствуете, что ваши операции по производительности могут быть главным беспокойством, то я предлагаю вам тест. Здесь тестовый скрипт с обновленной производительностью, для всех четырех версий. Вариант 1 медленный, а 3 DATEDIFF альтернативы (2-4) довольно близки к победной 4 альтернативе. Я также предлагаю вам рассмотреть удобочитаемость, которая является делом вкуса.

    Альтернативные DATEADD также обладают преимуществом гибкости. Вместо того, чтобы перепечатать предложения Hugo, я просто скопировал исходный текст Hugo в присланное мне письмо:

    Версия, которая использует строковое преобразование, может быть использована для отделения времени от текущей даты. DATEADD/DATEDIFF версия может быть легко изменена, для получения начала текущего месяца, квартала или года. SELECT DATEADD(month, DATEDIFF(month, ‘20000101’, CURRENT_TIMESTAMP), ‘20000101’) или к отказу только от минут и секунд, или только секунд SELECT DATEADD(hour, DATEDIFF(hour, ‘20000101’, CURRENT_TIMESTAMP), ‘20000101’) (Примечание – когда речь идет о минутах, секундах или миллисекундах, функция DATEDIFF может вызвать переполнение целочисленного диапазона – базовая дата могла бы быть адаптирована к ожидаемому набору значений datetime). Также возможны дальнейшие изменения: Как получить последний день предыдущего месяца в последний день текущего месяца?

    SELECT DATEADD(month, DATEDIFF(month, ‘20000101’, CURRENT_TIMESTAMP), ‘19991231’)
    SELECT DATEADD(month, DATEDIFF(month, ‘20000101’, CURRENT_TIMESTAMP), ‘20000131’)

    Как получить завтрашнюю дату (без времени)?

    SELECT DATEADD(day, DATEDIFF(day, ‘20000101’, CURRENT_TIMESTAMP), ‘20000102’)

    Как округлить datetime до ближайшего часа, или к ближайшему дню?

    SELECT DATEADD(hour, DATEDIFF(hour, ‘20000101’, DATEADD(minute, 30, CURRENT_TIMESTAMP)), ‘20000101’)
    SELECT DATEADD(day, DATEDIFF(day, ‘20000101’, DATEADD(hour, 12, CURRENT_TIMESTAMP)), ‘20000101’)

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

    SELECT DATEADD(day, (DATEDIFF (day, ‘20000107’, CURRENT_TIMESTAMP) / 7) * 7, ‘20000107’)

    или
    SELECT DATEADD(day, (DATEDIFF (day, ‘20000108’, CURRENT_TIMESTAMP) / 7) * 7, ‘20000107’)

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

    Совет: Всегда установить время в одно и то же значение

    Если вы не заинтересованы в сегменте времени, и вы не можете использовать тип дата (date), вы можете установить временную часть в стандартное значение для всех строк. Я рекомендую 00:00:00.000. Допустим, вы хотите установить значение текущей даты, когда выполняется INSERT. Для этого используем конструкцию DEFAULT:

    CREATE TABLE #dts(id INT IDENTITY, d_date DATETIME DEFAULT CONVERT(char(8), CURRENT_TIMESTAMP, 112))
    INSERT #dts DEFAULT VALUES
    SELECT * FROM #dts


    Я использую код 112 в вышеприведенной функции CONVERT(), которая возвращает строку с указанием только даты в формате «без разделительных знаков» (Unseparated). Тем не менее, мы не защищены от явной установки значения и присвоения сегменту времени чего-то отличного от 00:00:00:000.

    Мы можем сделать это с помощью CONSTRAINT-а:

    ALTER TABLE #dts ADD CONSTRAINT dts_chk_d_date CHECK(CONVERT(char(12), d_date, 114) = ’00:00:00:000′)

    Какие преимущества нам дает это выражение? Если мы желаем найти все строки, начиная с определенной даты, WHERE напишем таким образом:

    WHERE dt = ‘20040305’

    Этот метод можно рассматривать как более понятный и удобный для чтения. Кроме этого, если вы захотите объединить 2 таблицы, основываясь только на дне, теперь это можно сделать напрямую. Если у вас величина времени (часть содержащая время) изменяется, то придется использовать CONVERT в операциях JOIN, которые снова могут катастрофически влиять на производительность.
    Вышеизложенная технология применима, если вам интересна только величина времени, в таком случае я рекомендую Вам всегда устанавливать саму дату как 1900-01-01.
    Теперь, в SQL Server 2008 это не более чем отдельные типы date и time?

    Дополнительные советы от Steve Kass

    С осторожностью указывайте все даты в запросах типом smalldatetime, используя CAST или CONVERT. Отметим, что в колонке datetime SQL Server 7.0 (не менее) это может привести к непредвиденному результату.

    Везде без веских причин и пояснений, даты сохраняются только типом datetime или smalldatetime, а не в виде строки. Если даты должны быть сохранены в строковом виде (существует несколько хороших причин для этого, но их мало), то используйте нейтрально-языковой формат и 24-часовое представление если также сохраняете и время, так чтобы строки могли использоваться везде и также при сортировке дат. Например, можно использовать “dt < ‘20030101’” в предложении WHERE и запрос не возвращает ожидаемые строки, потому как dt хранится в виде строки в формате yyyy-mm-dd (гггг-мм-дд).
    В Enterprise Manager (и некоторых других инструментах и приложениях Microsoft) базовая дата это 1899-12-30. EM никогда не должен использоваться для записи данных, и редко для просмотра данных. Если вы используете EM для указания данных «только время», EM сохранит дату как 1899-12-30. Если вы действительно хотите использовать EM, укажите дату 1900-01-01 в явном виде. Отметим также, что EM отобразит только величину времени, если дата 1899-12-30, а не то, что вы ожидаете, если у вас в качестве стандартного значения даты используется 1900-01-01.

    Почему 1753 ближайшая дата для DATETIME?

    Хороший вопрос. Ответ: именно по историческим причинам. В современное время у нас (в том, что мы иногда называем западным миром) было два календаря: Юлианский и Григорианский календарь. Эти календари по нумерации дней отличались друг от друга (в зависимости от столетия на который вы посмотрите), поэтому, когда используемый Юлианский календарь заменили на Григорианский, было удалено от 10 до 13 дней. Великобритания сделала этот переход в 1752 году (дата 1752-09-02 стала 1752-09-14). Научное предположение, зачем Sybase выбрали 1753 в качестве ближайшей даты состоит в том, что при сохранении более ранней даты чем 1753, необходимо учитывать что это за страна, а также обработать этот 10-13дневный «прыжок». Поэтому они[Sybase] решили не позволять даты ранее 1753 года. Однако отметим, что прочие страны сделали это смещение позже 1752. Турция, например, это реализовала аж в 1927 году.
    Будучи шведом, автор находит забавной ситуацию самой фантастической реализация именно у Швеции. Было решено пропускать день сдвига в течение 40 лет (с 1700 до 1740), и Швеция была бы в синхронизации с Григорианским календарем после 1740 (но в течение этого времени, не в синхронизации с другими странами). Но по некоторым причинам, в 1704 и 1708 годах день сдвига был не пропущен, а в 1712 который был високосным годом, они вставили дополнительный день (представьте себе рожденных 30 февраля!), и затем в 1753 году сделали сдвиг в течение дня, как и все остальные.

    Список рекомендованной литературы

    Это, безусловно, самая сложная из найденных документация о календарях:

    Часто задаваемые вопросы о календарях, Claus Tøndering

    Книга Richard T. Snodgrass “Разработка временно-ориентированных приложений баз данных в SQL”: содержит много сведений о представлении время-ориентированной информации в модели данных. И, конечно же, возможно использование этой дополнительной (исторической) информации в своих запросах SQL. Эта книга не издается, а на официальном сайте Ричарда (www.cs.arizona.edu/people/rts), вы бесплатно можете скачать ее в PDF формате.

    Оригинал: The ultimate guide to the datetime datatypes

    Перевод: Винчик Евгений

Комментарии

  1. Автору спасибо за статью, очень интересна и позновательня оказалась для меня 🙂

  2. Согласен с мнением Антона. Статья зачетная, есть умные мысли и есть чему поучиться

  3. Отличная статья!

  4. Спасибо автору. А то я думал, что Старый Новый год только у нас в стране 🙂