Monday, December 14, 2009

Конвертация даты в MS SQL из varchar в datetime и обратно

Иногда бывает необходимо реализовать конвертацию даты, записанной в виде строки в тип datetime или наоборот. В TSQL есть функция CONVERT, которая позволяет выполнить такое преобразование. Например, пользовательская функция преобразования значения типа datetime в строку вида YYYY-MM-DD HH:MM:SS, может выглядеть так:

CREATE FUNCTION [dbo].[dt_to_str]
(
@dt datetime
)
RETURNS varchar(19)
AS
BEGIN
RETURN CONVERT(varchar(19), @dt, 120)
END

Однако использовать CONVERT для преобразования строки в datetime не безопасно, особенно если планируется теражировать базу. Может возникнуть ситуация, когда это преобразование будет завершаться с ошибкой из-за неверного формата записи даты.
Более безопасный способ преобразования - использование функции dateadd. Известно, что datetime отсчитывается от 1 января 1900 года. Этой дате соответствует нулевое значение, проверьте:

SELECT cast(0 as datetime)

Соответственно, находя разницу с этой нулевой датой и прибавляя её к ней мы можем получить представление строки в виде даты типа datetime. Для начала простой пример:

ALTER FUNCTION [dbo].[make_date]
(
@day int,
@month int,
@year int
)
RETURNS datetime
AS
BEGIN
DECLARE @dt datetime
SET @dt = cast(0 as datetime)

-- прибавляем года
SELECT @dt = dateadd(year, (@year - 1900), @dt)
-- прибавляем месяцы
SELECT @dt = dateadd(month, (@month - 1), @dt)
-- прибавляем дни
SELECT @dt = dateadd(day, (@day - 1), @dt)

RETURN @dt
END

Эта функция получает в качестве целочисленных параметров день, месяц и год, а возвращает соответствующую им дату в формате datetime.

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

ALTER FUNCTION [dbo].[int_to_datetime]
(
@val bigint
)
RETURNS datetime
AS
BEGIN
DECLARE @str varchar(12)
SET @str = CAST(@val AS varchar)

IF LEN(@str) < 12 -- Формат числа неверный
OR CAST(SUBSTRING(@str, 5, 2) AS int) > 12 -- Месяц неверный
OR CAST(SUBSTRING(@str, 7, 2) AS int) > 31 -- День
OR CAST(SUBSTRING(@str, 9, 2) AS int) > 23 -- Час
OR CAST(SUBSTRING(@str, 11, 2) AS int) > 60 -- Минута
RETURN NULL

DECLARE @dt datetime
SET @dt = cast(0 as datetime)

-- извлечение года
SELECT @dt = dateadd(year, (SUBSTRING(@str, 1, 4) - 1900), @dt)
-- извлечение месяца
SELECT @dt = dateadd(month, (SUBSTRING(@str, 5, 2) - 1), @dt)
-- извлечение дня
SELECT @dt = dateadd(day, (SUBSTRING(@str, 7, 2) - 1), @dt)
-- извлечение часа
SELECT @dt = dateadd(hour, CAST(SUBSTRING(@str, 9, 2) AS int), @dt)
-- извлечение минуты
SELECT @dt = dateadd(minute, CAST(SUBSTRING(@str, 11, 2) AS int), @dt)

RETURN @dt
END

Работает это преобразование быстро и вы не зависите от установок формата даты.

1 comment:

vlad275 said...

SELECT EXTRACT(
MONTH FROM `start_time` )
FROM sdr