2.21. Конвертирование типов

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

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

Для преобразования данных используются команда (функция) CAST и CONVERT. В общем, виде CAST выглядит следующим образом:

CAST ( expression AS data_type )

Рассмотрим классический пример. Допустим, что нам необходимо сложить два числовых поля. Для этого возьмем из таблицы tbPeoples поля "idPeoples" и "idPosition". Попробуем выполнить следующий запрос:

SELECT idPeoples+idPosition
FROM tbPeoples

В результате мы получим таблицу с результатами арифметического сложения этих полей. А если нужно сложить эти колонки как строки? То есть '1'+'1' в результате должно дать '11', а не арифметическую сумму (число 2). Как решить эту проблему? Нужно просто привести значения этих колонок к строке:

SELECT cast(idPeoples as varchar(10)) + 
   cast(idPosition as varchar(10))
FROM tbPeoples

Что здесь происходит? Каждое поле мы приводим к строковому типу varchar размером в 10 символов. Например, первое поле преобразуется к строке следующим образом:

cast(idPeoples as varchar(10))

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

Открою небольшой секрет. Автоматическое преобразование в SQL сервере все же есть. Как вы думаете, что будет, если выполнить следующий запрос:

SELECT cast(idPeoples as varchar(10)) + idPosition
FROM tbPeoples

В этом примере первое поле приводиться к строке, а второе остается числом. Что будет результатом сложения строки с числом? Если вы попробуете выполнить этот запрос, то увидите, что результатом будет арифметическое сложение двух чисел. Не смотря на то, что первую колонку мы привели к строке, во время выполнения запроса SQL сервер автоматически вернул этому полю числовой тип и сложил колонки как числа. Таким образом, автоматическое преобразование есть, но автоматически можно привести далеко не все типы.

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

Во время преобразования нужно быть внимательным и при указании типа и размера. Например, следующий запрос приводит слово 'Привет' к типу char размером в 10 символов. Для наглядности я прибавляю к результату содержимое поля "vcFamil", где у нас храниться фамилия работника:

SELECT CAST('Привет ' AS char(10)) + vcFamil
FROM tbPeoples

Результат может быть неожиданным. Например:

Привет    mr.ИВАНОВ

Обратите внимание, что между словом 'Привет' и содержимым поля несколько пробелов, а ведь мы их не указывали. Это связано с правилами использования типа char. Дело в том, что переменные или поля этого типа полностью занимают отведенное пространство. Если переменная требует меньшее пространство (как слово 'Привет', требует только 6 символов), то оставшееся пространство заполняется пробелами. Преобразуемое слово занимает 6 символов, а мы его приводим к типу char из 10 символов. Это значит, что после в конце строки будет добавлено 4 пробела, чтобы строка занимала все отведенное пространство.

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

CONVERT ( data_type [ ( length ) ] , 
   expression [ , style ] )

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

SELECT CONVERT(decimal(10,5), idPeoples)
FROM tbPeoples

С помощью команд CAST и CONVERT можно округлять числа. Например, в таблице товаров у нас есть поле для хранения цены, которое является дробным. Давайте округлим цену до рублей, отбросив все, что находится после запятой, т.е. копейки:

-- Округление с помощью CONVERT
SELECT CONVERT(decimal(10,0), Цена)
FROM Товары

-- Округление с помощью CAST
SELECT CAST(Цена AS decimal(10,0))
FROM Товары

Можно было бы привести и к целочисленному типу, но я решил использовать тип decimal, но явно указать, что после запятой должно быть 0 разрядов. В следующем примере после запятой остается только 1 разряд:

SELECT CONVERT(decimal(10,1), Цена)
FROM Товары

2.22. Работа с датами и временем

Если вы посмотрите приложение 1, то увидите, что тип datetime может принимать значения от 1 Января 1753 и до 31 декабря 9999. Если посмотреть на дату 1 января 2005 года, то это будет число с плавающей точкой, означающее количество времени с точностью до 3.33 миллисекунды. Целая часть – это дата, а дробная часть – это время. В разделе 2.17 мы уже использовали особенность даты быть числом для увеличения даты на один день. В этой главе мы поговорим о датах более подробно.

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. Стили преобразования типа данных даты

Исходя из этой таблицы, стиль 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, установленное значение действует в течение всей сессии. Но если отключиться и подключиться к серверу заново, сервер снова будет требовать ввода даты в формате по умолчанию.

2.22.2. Функции для работы с датами

Но на преобразовании весь свет не сошелся. Есть еще множество функций, встроенных в SQL сервер, с помощью которых можно работать с типом даты. В таблице 2.2 вы можете увидеть функции, которые есть у MS SQL Server для работы с датами и краткое описание. Далее мы рассмотрим примеры работы с этими функциями.

Таблица 2.2. Функции MS SQL Server по работе с датами

Текущая дата

Наиболее часто используемая функция – GETDATE(), которая текущую дату. Если в таблице необходимо сохранять текущую дату, то лучше всего будет определять ее с помощью функции сервера, а не с помощью каких-либо сторонних средств (например, с помощью клиентской программы) на компьютере клиента. Почему? Во-первых, если в вашей сети компьютеры не настроены на синхронизацию времени, то часы на всех компьютерах могут идти по-разному. Во-вторых, за работу часов при выключенном питании в компьютере отвечает маленькая батарейка, которая имеет тенденцию разряжаться, и тогда часы начинают серьезно отставать.

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

Я рекомендую для полей, которые должны отражать дату создания записи, еще при создании таблиц установить значение по умолчанию в виде функции GETDATE(). Например:

CREATE TABLE TestTable 
(
 id int DEFAULT 1,
 dDate datetime DEFAULT (getdate()),
 vcName varchar(50) DEFAULT 'M'
)

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

Если вы не указали значение по умолчанию для поля типа datetime, то используйте функцию GETDATE() в запросе:

INSERT INTO Товары
 (Дата, [Название товара], Цена, Количество) 
VALUES(GETDATE(), 'Сок', 23, 1)

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

Увеличение даты

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

DATEADD (datepart, number, date)

В скобках указывается три параметра:

1. datepart – какую часть даты необходимо увеличить. Возможные значения этого параметра можно увидеть на 1;
2. number – число, на которое надо увеличить;
3. date – дата, которую надо изменить.

Таблица 2.3. Возможные значения параметра datepart

Допустим, что необходимо увеличить дату на 1 год. Для этого можно выполнить следующий запрос:

SELECT DATEADD(Year, 1, GETDATE())

Чтобы уменьшить значение даты, необходимо в качестве второго параметра указать отрицательное значение. Например, следующий пример уменьшает значение текущей даты на 1:

SELECT DATEADD(Year, -1, GETDATE())

Разница в дате

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

Разницу можно определить с помощью функции DATEDIFF, которая имеет следующий вид:

DATEDIFF ( datepart , startdate , enddate )

Здесь также три параметра:

• Часть даты, разницу в которой необходимо определить. Возможные значения показаны в таблице 2.3;
• Начальная дата;
• Конечная дата.

Следующая команда определяет количество дней между двумя датами:

SELECT DATEDIFF(Day, '03.04.2005', '07.08.2005')

В результате на экране мы должны увидеть число 126 дней.

Имя даты

Некоторые пользователи любят смотреть на даты в виде символьных имен. Для этого используется функция DATENAME:

DATENAME ( datepart , date )

Посмотрим параметры этой функции:

• Часть даты, символьное имя в которой необходимо определить. Возможные значения показаны в таблице 2.3;
• Дата;

Несмотря на то, что в качестве первого параметра можно указывать любую часть даты, смысла от этого особо нет. Только при указании месяца будет возвращено символьное имя. Например, следующий запрос вернет нам в результате название 4-го месяца, т.е. April:

SELECT DATENAME(Month, '04.03.2005')

Если попытаться узнать символьное имя числа, то функция вернет нам это число.

Разбиение даты на части

Для определения определенной части даты можно одну из функций DAY, MONTH или YEAR (рассмотрим ниже в этой главе), но они ограничены в возможностях и заточены под определенную задачу. Более универсальной является функция DATEPART, которая имеет следующий внешний вид:

DATEPART ( datepart , date )

Параметры функции:

• Часть даты, значение которой необходимо определить. Возможные значения показаны в таблице 2.3;
• Дата;

Следующий пример определяет значение месяца в дате:

SELECT DATEPART(Month, '04.03.2005')

В результате мы получим число 4.

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

• DAY(дата) – возвращает значение числа, указанное в дате;
• MONTH(дата) – возвращает значение месяца;
• YEAR(дата) - возвращает значение года.

Например, с помощью следующего запроса определяется год, который указан в дате '04.03.2005':

SELECT YEAR('04.03.2005')

2.22.3. Замечания по работе с датами

С типом datetime достаточно сложно работать. Рассмотрим следующие три запроса:

SELECT 
FROM Товары
WHERE Дата

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

SELECT 
FROM Товары
WHERE Дата

В этом запросе по идее запрашивается то же самое, ведь мы просим товары, купленные до 10-го числа включительно. Знак меньше заменен на «меньше либо равно», а дата уменьшена на 1 день. Вы удивитесь, но результат может быть другим. Почему? Для этого нужно вспомнить, что представляет собой тип datetime. Если в поле для даты сохраняется не только дата, но и время, то числа будут дробными, а мы сравниваем только с датой.

Возьмем число 10-е мая 2005-го года. Этой дате соответствует число 38481. Но если помимо даты в поле будет храниться и время, то число будет дробным, например, 38481,1943. Такое число удовлетворяет условию меньше '5.11.2005' (38481,1943 < 38482), но не удовлетворяет условию меньше либо равно '5.10.2005' (38481,1943

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