Функция DATEDIFF Синтаксис DATEDIFF ( datepart , startdate , enddate ) Функция возвращает интервал времени, прошедшего между двумя временными отметками — startdate (начальная отметка) и enddate

Робота з датою та геоінформацією в SQL Server 2008

1. Функції Transact-SQL для обробки дати/часу
Стандарт SQL-92 специфицирует только функции, возвращающие системную дату/время. Например, функция CURRENT_TIMESTAMP возвращает сразу и дату, и время. Плюс имеются функции возвращающие что-либо одно.
Естественно, в силу такой ограниченности, реализации языка расширяют стандарт за счет добавления функций, облегчающий работу пользователей с данными этого типа. Здесь мы рассмотрим функции обработки даты/времени в T-SQL.
Функция DATEADD
Синтаксис
DATEADD ( datepart , number, date )
Эта функция возвращает значение типа datetime, которое получается добавлением к дате date количества интервалов типа datepart, равного number. Например, мы можем к заданной дате добавить любое число лет, дней, часов, минут и т.д. Допустимые значения аргумента datepart приведены ниже и взяты из BOL.
Datepart
Допустимые сокращения

Year - год
yy, yyyy

Quarter - квартал
qq, q

Month - месяц
mm, m

Dayofyear - день года
dy, y

Day - день
dd, d

Week - неделя
wk, ww

Hour - час
hh

Minute - минута
mi, n

Second - секунда
ss, s

Millisecond - миллисекунда
ms

Пусть сегодня 23/01/2004, и мы хотим узнать, какой день будет через неделю. Мы можем написать
SELECT DATEADD(day, 7, current_timestamp)

а можем и так
SELECT DATEADD(ww, 1, current_timestamp)

В результате получим одно и то же; что-то типа 2004-01-30 19:40:58.923. Однако мы не можем в этом случае написать
SELECT DATEADD(mm, 1/4, current_timestamp)

потому, что дробная часть значения аргумента datepart отбрасывается, и мы получим 0 вместо одной четвертой и, как следствие, текущий день. Кроме того, мы можем использовать вместо CURRENT_TIMESTAMP функцию T-SQL GETDATE() с тем же самым эффектом. Наличие двух идентичных функций поддерживается, видимо, в ожидании последующего развития стандарта. Пример (схема 4). Определить, какой будет день через неделю после последнего полета.
SELECT DATEADD(day, 7, (SELECT MAX(date) max_date FROM pass_in_trip))

Использование подзапроса в качестве аргумента допустимо, т.к. этот подзапрос возвращает ЕДИНСТВЕННОЕ значение типа datetime.
Функция DATEDIFF
Синтаксис
DATEDIFF ( datepart , startdate , enddate )
Функция возвращает интервал времени, прошедшего между двумя временными отметками - startdate (начальная отметка) и enddate (конечная отметка). Этот интервал может быть измерен в разных единицах. Возможные варианты определяются аргументом datepart и перечислены выше применительно к функции DATEADD. Пример (схема 4). Определить количество дней, прошедших между первым и последним совершенными рейсами.
SELECT DATEDIFF(dd, (SELECT MIN(date) FROM pass_in_trip), (SELECT MAX(date) FROM pass_in_trip))

Пример (схема 4). Определить продолжительность рейса 1123 в минутах. Здесь следует принять во внимание, что время вылета (time_out) и время прилета (time_in) хранится в полях типа datetime таблицы Trip. Заметим, что SQL Server вплоть до версии 2000 не имеет отдельных темпоральных типов данных для даты и времени, появление которых ожидается в следующей версии (Yukon). Поэтому при вставке в поле datetime только времени (например, UPDATE trip SET time_out = '17:24:00' WHERE trip_no=1123), время будет дополнено значением даты по умолчанию ('1900-01-01'). Напрашивающееся решение
SELECT DATEDIFF(mi, time_out, time_in) dur FROM trip WHERE trip_no=1123,

(которое дает -760) будет неверным по двум причинам. Во-первых, для рейсов, которые вылетают в один день, а прилетают на следующий, вычисленное таким способом значение будет неправильным. Во-вторых, ненадежно делать какие либо предположения относительно дня, который присутствует только в силу необходимости соответствовать типу datetime. Но как определить, что самолет приземлился на следующий день? Тут помогает описание предметной области, где говорится, что полет не может продолжаться более суток. Итак, если время прилета не больше, чем время вылета, то этот факт имеет место. Теперь второй вопрос: как посчитать только время, с каким бы днем оно ни стояло? Здесь может помочь функция T-SQL DATEPART.
Функция DATEPART
Синтаксис
DATEPART ( datepart , date )
Эта функция возвращает целое число, представляющее собой указанную аргументом datepart часть заданной вторым аргументом даты (date). Список допустимых значений аргумента datepart, описанный выше в данном разделе, дополняется еще одним значением
Datepart
Допустимые сокращения

Weekday - день недели
dw

Заметим, что возвращаемое функцией DATEPART значение в этом случае (номер дня недели) зависит от настроек, которые можно изменить с помощью оператора SET DATEFIRST, устанавливающего первый день недели. Для кого-то понедельник - день тяжелый, а для кого-то - воскресенье. Кстати, последнее значение принимается по умолчанию. Однако вернемся к нашему примеру. В предположении, что время вылета/прилета является кратным минуте, мы можем его определить как сумму часов и минут. Поскольку функции даты/времени работают с целочисленными значениями, приведем результат к наименьшему интервалу - минутам. Итак, время вылета рейса 1123 в минутах
SELECT DATEPART(hh, time_out)*60 + DATEPART(mi, time_out) FROM trip WHERE trip_no=1123

и время прилета
SELECT DATEPART(hh, time_in)*60 + DATEPART(mi, time_in) FROM trip WHERE trip_no=1123

Теперь мы должны сравнить, превышает ли время прилета время вылета. Если это так, вычесть из первого второе, чтобы получить продолжительность рейса. В противном случае к разности нужно добавить одни сутки (24*60 = 1440 минут).
SELECT CASE WHEN time_dep>=time_arr THEN time_arr-time_dep+1440 ELSE time_arr-time_dep END dur FROM ( SELECT DATEPART(hh, time_out)*60 + DATEPART(mi, time_out) time_dep, DATEPART(hh, time_in)*60 + DATEPART(mi, time_in) time_arr FROM trip WHERE trip_no=1123 ) tm

Здесь, чтобы не повторять длинные конструкции в операторе CASE, использован подзапрос. Конечно, результат получился достаточно громоздким, зато абсолютно корректным в свете сделанных к этой задаче замечаний. Пример (4 схема). Определить дату и время вылета рейса 1123. В таблице совершенных рейсов Pass_in_trip содержится только дата рейса, но не время, т.к. в соответствии с предметной областью каждый рейс может выполняться только один раз в день. Для решения этой задачи нужно к дате, хранящейся в таблице Pass_in_trip, добавить время из таблицы Trip
SELECT pt.trip_no, DATEADD(mi, DATEPART(hh,time_out)*60 + DATEPART(mi,time_out), date) [time] FROM pass_in_trip pt JOIN trip t ON pt.trip_no=t.trip_no WHERE t.trip_no=1123

Выполнив запрос, получим следующий результат
Trip_no
Time

1123
2003-04-05 16:20:00.000

1123
2003-04-08 16:20:00.000

DISTINCT необходим здесь, чтобы исключить возможные дубликаты, поскольку номер и дата рейса дублируются в этой таблице для каждого пассажира данного рейса.
Функция DATENAME
Синтаксис
DATENAME ( datepart , date )
Эта функция возвращает символьное представление составляющей (datepart ) указанной даты (date). Аргумент, определяющий составляющую даты, может принимать одно из значений, перечисленных в вышеприведенной таблице. Это дает нам простую возможность конкатенировать компоненты даты, получая любой нужный формат представления. Например, конструкция
SELECT DATENAME ( weekday , '2003-12-31' )+', '+DATENAME ( day , '2003-12-31' )+' '+ DATENAME ( month , '2003-12-31' )+' '+DATENAME ( year , '2003-12-31' )

даст нам следующий результат
Wednesday, 31 December 2003

Следует отметить, что данная функция выявляет отличие значений day и dayofyear аргумента datepart. Первый дает символьное представление дня указанной даты, в то время как второй дает символьное представление этого дня от начала года. Т.е.
SELECT DATENAME ( day , '2003-12-31' )

даст нам 31, а
SELECT DATENAME ( dayofyear , '2003-12-31' )

- 365. В ряде случаев функцию DATEPART можно заменить более простыми функциями. Вот они: DAY ( date )  -  целочисленное представление дня указанной даты. Эта функция эквивалентна функции DATEPART(dd, date). MONTH ( date )   -  целочисленное представление месяца указанной даты. Эта функция эквивалентна функции DATEPART(mm, date). YEAR ( date )   -  целочисленное представление года указанной даты. Эта функция эквивалентна функции DATEPART(yy, date).
date (Transact-SQL)
Описывает дату.
Обзор всех типов данных и функций даты и времени в языке Transact-SQL см. в разделе [ Cкачайте файл, чтобы посмотреть ссылку ]. Сведения и примеры, относящиеся к типам данных и функциям даты и времени, см. в разделе [ Cкачайте файл, чтобы посмотреть ссылку ].
[ Cкачайте файл, чтобы посмотреть картинку ] Описание типа данных date
Свойство
Значение

Синтаксис
date

Сведения об использовании
DECLARE @MyDate date
CREATE TABLE Таблица1 ( Столбец1 date )

Формат строковых литералов по умолчанию
(используется для клиента нижнего уровня)
ГГГГ-ММ-ДД
Дополнительные сведения см. в подразделе «Обратная совместимость для клиентов нижнего уровня» раздела [ Cкачайте файл, чтобы посмотреть ссылку ].

Диапазон
От 0001-01-01 до 9999-12-31
От 1 января 1 года до 31 декабря 9999 года

Диапазоны элементов
ГГГГ обозначает 4 цифры, которые представляют год и принимают значения от 0001 до 9999.
ММ обозначает 2 цифры, которые представляют месяц и принимают значения от 01 до 12.
ДД обозначает две цифры, представляющие день указанного месяца и принимающие значения от 01 до 31 в зависимости от месяца.

Длина в символах
10 позиций

Точность, масштаб
10, 0

Объем памяти
3 байта, фиксированный

Структура хранилища
Дата хранится в одной переменной типа integer размером 1 или 3 байта.

Точность
Один день

Значение по умолчанию
1900-01-01
Данное значение используется как присоединяемая часть даты при неявном преобразовании данных типа time в значение типа datetime2 или datetimeoffset.

Календарь
Григорианский

Определяемая пользователем точность в долях секунды
Нет

Учет и сохранение смещения часового пояса
Нет

Учет перехода на летнее время
Нет

[ Cкачайте файл, чтобы посмотреть картинку ] Поддерживаемые форматы строковых литералов для типа данных date
В следующей таблице перечислены допустимые форматы строковых литералов для типа данных date.
Числовой
Описание

мдг
[м]м/дд/[гг]гг
[м]м-дд-[гг]гг
[м]м.дд.[гг]гг
мгд
мм/[гг]гг/дд
мм-[гг]гг/дд
[м]м.[гг]гг.дд
дмг
дд/[м]м/[гг]гг
дд-[м]м-[гг]гг
дд.[м]м.[гг]гг
дгм
дд/[гг]гг/[м]м
дд-[гг]гг-[м]м
дд.[гг]гг.[м]м
гмд
[гг]гг/[м]м/дд
[гг]гг-[м]м-дд
[гг]гг-[м]м-дд
Сочетания [м]м, дд и [гг]гг представляют в строке месяц, день и год, разделенные символом косой черты (/), дефисом (-) или точкой (.).
Поддерживаются только двух- и четырехзначные форматы записи года. Для записи года рекомендуется всегда использовать четырехзначный формат. Чтобы указать целое число в промежутке от 0001 до 9999, отображающее пороговое значение года при преобразовании двухзначной записи в четырехзначную, необходимо использовать [ Cкачайте файл, чтобы посмотреть ссылку ].
Если двузначное число, обозначающее год, меньше или равно двузначному числу из последних двух цифр порогового года, год относится к тому же столетию, что и пороговый год. Если двузначное число, обозначающее год, больше двузначного числа из последних двух цифр порогового года, год относится к столетию, которое предшествует столетию порогового года. Например, если пороговое значение года для двухзначной записи равно 2049, то год, обозначенный двумя цифрами 49, интерпретируется как 2049, а год, обозначенный двумя цифрами 50, интерпретируется как 1950.
Формат даты по умолчанию определяется текущими настройками языковых стандартов. Формат даты можно изменить с помощью инструкций [ Cкачайте файл, чтобы посмотреть ссылку ] и [ Cкачайте файл, чтобы посмотреть ссылку ].
Формат ydm (гдм) не поддерживается для типа данных date.





Алфавитный формат
Описание

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


ISO 8601
Описание

ГГГГ-ММ-ДД
ГГГГММДД
То же, что и стандарт SQL. Является единственным международным стандартом.


Без разделителей
Описание

[гг]ггммдд
гггг[мм][дд]
Данные типа date могут быть заданы с помощью четырех, шести или восьми цифр. Строка из шести или восьми знаков всегда интерпретируется как ymd (гмд). На месяц и день всегда должно приходиться по две цифры. Строка из четырех цифр воспринимается как значение года.


Интерфейс ODBC
Описание

{ д 'гггг-мм-дд' }
Зависит от API-интерфейса ODBC.
Работает в SQL Server 2008 так же, как и в SQL Server 2005.


Формат W3C XML
Описание

гггг-мм-ддTZD
Специально введен для использования в XML и SOAP.
TZD обозначает часовой пояс (в виде номера или смещения +чч:мм и -чч:мм):
«чч:мм» обозначает смещение временного пояса. Обозначение «чч» состоит из двух цифр, представляющих смещение в часах, и принимает значения от 0 до 14.
Обозначение «мм» состоит из двух цифр, представляющих дополнительное смещение временного пояса в минутах, и принимает значения от 0 до 59.
+ (плюс) или – (минус) представляет собой обязательный знак смещения часового пояса. Данный знак указывает, добавляется или вычитается смещение часового пояса от времени по Гринвичу (формат UTC), чтобы получить локальное время. Допустимый диапазон смещения часового пояса: от -14:00 до +14:00.

[ Cкачайте файл, чтобы посмотреть картинку ] Соответствие стандартам ANSI и ISO 8601
date соответствует стандартному определению ANSI SQL для григорианского календаря: "Замечание 85 Данные типа Datetime позволяют хранить даты григорианского календаря в виде чисел диапазона от 0001–01–01 CE до 9999–12–31 CE."
Формат строковых литералов по умолчанию, используемый для клиентов нижнего уровня, соответствует стандарту SQL, в котором он определен как ГГГГ-ММ-ДД. Данный формат аналогичен определению даты в стандарте ISO 8601.
[ Cкачайте файл, чтобы посмотреть картинку ] Пример
В следующем примере сравниваются результаты приведения строкового типа к каждому из типов данных даты и времени.
SELECT
CAST('2007-05-08 12:35:29. 1234567 +12:15' AS time(7)) AS 'time'
,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS date) AS 'date'
,CAST('2007-05-08 1
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·
·Ниже приводится результирующий набор.


Тип данных
Результат

time
12:35:29. 1234567

date
2007-05-08

smalldatetime
2007-05-08 12:35:00

datetime
2007-05-08 12:35:29.123

datetime2
2007-05-08 12:35:29. 1234567

datetimeoffset
2007-05-08 12:35:29.1234567 +12:15


Типы данных и функции даты и времени (Transact-SQL)
Типы данных даты и времени Transact-SQL перечислены в следующей таблице.
Тип данных
Format
Диапазон
Точность
Объем памяти (в байтах)
Определяемая пользователем точность в долях секунды
Смещение часового пояса

[ Cкачайте файл, чтобы посмотреть ссылку ]
чч:мм:сс[.ннннннн]
От 00:00:00.0000000 до 23:59:59.9999999
100 наносекунд
От 3 до 5
Да
Нет

[ Cкачайте файл, чтобы посмотреть ссылку ]
ГГГГ-ММ-ДД
От 0001-01-01 до 9999-12-31
1 день
3
Нет
Нет

[ Cкачайте файл, чтобы посмотреть ссылку ]
ГГГГ-ММ-ДД чч:мм:сс
От 01.01.1900 до 06.06.2079
1 минута
4
Нет
Нет

[ Cкачайте файл, чтобы посмотреть ссылку ]
ГГГГ-ММ-ДД чч:мм:сс[.ннн]
От 01.01.1753 до 9999-12-31
0,00333 секунда
8
Нет
Нет

[ Cкачайте файл, чтобы посмотреть ссылку ]
ГГГГ-ММ-ДД чч:мм:сс[.ннннннн]
От 0001-01-01 00:00:00.0000000 до 9999-12-31 23:59:59.9999999
100 наносекунд
От 6 до 8
Да
Нет

[ Cкачайте файл, чтобы посмотреть ссылку ]
ГГГГ-ММ-ДД чч:мм:сс[.ннннннн] [+|-]чч:мм
От 0001-01-01 00:00:00.0000000 до 9999-12-31 23:59:59.9999999 (время в формате UTC)
100 наносекунд
От 8 до 10
Да
Да


[ Cкачайте файл, чтобы посмотреть картинку ]Примечание.

Тип данных [ Cкачайте файл, чтобы посмотреть ссылку ] языка Transact-SQL не является типом данных времени или даты. Тип timestamp устаревший синоним типа rowversion.

[ Cкачайте файл, чтобы посмотреть картинку ] Функции даты и времени
Функции даты и времени Transact-SQL перечислены в следующих таблицах. Дополнительные сведения о детерминизме см. в разделе [ Cкачайте файл, чтобы посмотреть ссылку ].
Функции, получающие значения системной даты и времени
Все значения системной даты и времени наследуется от операционной системы компьютера, на котором работает экземпляр SQL Server.
Высокоточные функции системной даты и времени
SQL Server 2008 получает значения даты и времени с помощью функции GetSystemTimeAsFileTime() Windows API. Точность зависит от физического оборудования и версии Windows, в которой запущен экземпляр SQL Server. Точность возвращаемых значений этого API-интерфейса задана равной 100 нс. Точность может быть определена с помощью метода GetSystemTimeAdjustment() API-интерфейса Windows.
Функция
Синтаксис
Возвращаемое значение
Тип возвращаемых данных
Детерминизм

[ Cкачайте файл, чтобы посмотреть ссылку ]
SYSDATETIME ()
Возвращает значение типа datetime2(7), которое содержит дату и время компьютера, на котором запущен экземпляр SQL Server. Смещение часового пояса не включается.
datetime2(7)
Недетерминированная

[ Cкачайте файл, чтобы посмотреть ссылку ]
SYSDATETIMEOFFSET ( )
Возвращает значение типа datetimeoffset(7), которое содержит дату и время компьютера, на котором запущен экземпляр SQL Server. Смещение часового пояса включается.
datetimeoffset(7)
Недетерминированная

[ Cкачайте файл, чтобы посмотреть ссылку ]
SYSUTCDATETIME ( )
Возвращает значение типа datetime2(7), которое содержит дату и время компьютера, на котором запущен экземпляр SQL Server. Возвращаемые дата и время отображаются в формате UTC.
datetime2(7)
Недетерминированная

Функции системной даты и времени меньшей точности
Функция
Синтаксис
Возвращаемое значение
Тип возвращаемых данных
Детерминизм

[ Cкачайте файл, чтобы посмотреть ссылку ]
CURRENT_TIMESTAMP
Возвращает значение типа datetime2(7), которое содержит дату и время компьютера, на котором запущен экземпляр SQL Server. Смещение часового пояса не включается.
datetime
Недетерминированная

[ Cкачайте файл, чтобы посмотреть ссылку ]
GETDATE ( )
Возвращает значение типа datetime2(7), которое содержит дату и время компьютера, на котором запущен экземпляр SQL Server. Смещение часового пояса не включается.
datetime
Недетерминированная

[ Cкачайте файл, чтобы посмотреть ссылку ]
GETUTCDATE ( )
Возвращает значение типа datetime2(7), которое содержит дату и время компьютера, на котором запущен экземпляр SQL Server. Возвращаемые дата и время отображаются в формате UTC.
datetime
Недетерминированная

Функции, получающие компоненты даты и времени
Функция
Синтаксис
Возвращаемое значение
Тип возвращаемых данных
Детерминизм

[ Cкачайте файл, чтобы посмотреть ссылку ]
DATENAME ( datepart, date )
Возвращает строку символов, представляющую указанную часть datepart заданной даты.
nvarchar
Недетерминированная

[ Cкачайте файл, чтобы посмотреть ссылку ]
DATEPART ( datepart, date )
Возвращает целое число, представляющее указанный компонент datepart указанной даты date.
int
Недетерминированная

[ Cкачайте файл, чтобы посмотреть ссылку ]
DAY ( date )
Возвращает целое число, представляющее день указанной даты date.
int
Детерминированное

[ Cкачайте файл, чтобы посмотреть ссылку ]
MONTH ( date )
Возвращает целое число, представляющее месяц указанной даты date.
int
Детерминированное

[ Cкачайте файл, чтобы посмотреть ссылку ]
YEAR ( date )
Возвращает целое число, представляющее год указанной даты date.
int
Детерминированное

Функции, получающие разность даты и времени
Функция
Синтаксис
Возвращаемое значение
Тип возвращаемых данных
Детерминизм

[ Cкачайте файл, чтобы посмотреть ссылку ]
DATEDIFF ( datepart, startdate , enddate )
Возвращает количество границ datepart даты или времени, пересекающихся между двумя указанными датами.
int
Детерминированное

Функции, изменяющие значения даты и времени
Функция
Синтаксис
Возвращаемое значение
Тип возвращаемых данных
Детерминизм

[ Cкачайте файл, чтобы посмотреть ссылку ]
DATEADD (datepart, number , date )
Возвращает новое значение datetime, добавляя интервал к указанной части datepart заданной даты date.
Тип данных аргумента date
Детерминированное

[ Cкачайте файл, чтобы посмотреть ссылку ]
SWITCHOFFSET (DATETIMEOFFSET , time_zone)
SWITCHOFFSET изменяет смещение часового пояса для значения DATETIMEOFFSET и сохраняет значение UTC.
datetimeoffset с точностью в долях секунд DATETIMEOFFSET
Детерминированное

[ Cкачайте файл, чтобы посмотреть ссылку ]
TODATETIMEOFFSET (expression , time_zone)
TODATETIMEOFFSET преобразует значение типа datetime2 в значение типа datetimeoffset. Значение datetime2 преобразуется в местное время для указанного time_zone.
datetimeoffset с точностью в долях секунд аргумента datetime
Детерминированное

Функции, устанавливающие или получающие формат сеанса
Функция
Синтаксис
Возвращаемое значение
Тип возвращаемых данных
Детерминизм

[ Cкачайте файл, чтобы посмотреть ссылку ]
@@DATEFIRST
Возвращает текущее значение параметра SET DATEFIRST для сеанса.
tinyint
Недетерминированная

[ Cкачайте файл, чтобы посмотреть ссылку ]
SET DATEFIRST { number | @number_var }
Устанавливает первый день недели в виде числа от 1 до 7.
Не применимо
Не применимо

[ Cкачайте файл, чтобы посмотреть ссылку ]
SET DATEFORMAT { format | @format_var }
Задает порядок составляющих даты (месяц/день/год) для ввода данных типа datetime или smalldatetime.
Не применимо
Не применимо

[ Cкачайте файл, чтобы посмотреть ссылку ]
@@LANGUAGE
Возвращает название используемого в данный момент языка. @@LANGUAGE не является функцией даты или времени. Однако на данные, выводимые функциями даты, могут повлиять настройки языка.
Не применимо
Не применимо

[ Cкачайте файл, чтобы посмотреть ссылку ]
SET LANGUAGE { [ N ] 'language' | @language_var }
Устанавливает языковую среду сеанса и системных сообщений. SET LANGUAGE не является функцией даты или времени. Однако на данные, выводимые функциями даты, влияет параметр языка.
Не применимо
Не применимо

[ Cкачайте файл, чтобы посмотреть ссылку ]
sp_helplanguage [ [ @language = ] 'language' ]
Возвращает сведения о форматах даты и всех поддерживаемых языках. Процедура sp_helplanguage не является хранимой процедурой даты или времени. Однако на данные, выводимые функциями даты, влияет параметр языка.
Не применимо
Не применимо

Функции, проверяющие значения даты и времени
Функция
Синтаксис
Возвращаемое значение
Тип возвращаемых данных
Детерминизм

[ Cкачайте файл, чтобы посмотреть ссылку ]
ISDATE ( expression )
Определяет, является ли входное выражение типа datetime или smalldatetime допустимым значением даты или времени.
int
Функция ISDATE детерминирована, только если используется совместно с функцией CONVERT и если заданный параметр стиля CONVERT не равен 0, 100, 9 или 109.


2. Представлення геоінформації

OGC – интернациональный промышленный консорциум, включающий в себя более 350 компаний, правительственных учреждений и университетов, которые собрались для разработки общедоступных спецификаций интерфейсов. Эти спецификации, названные спецификациями OpenGIS, поддерживают функционально совместимые решения, которые открывают «геодоступ» к Web-, беспроводным и локальным службам наряду с широко распространенной информационной технологией.
Основная идея, положенная в их основу – позволить разработчикам обеспечивать доступ пользователям к сложной пространственной информации и службам, чтобы эту информацию легко можно было разделять между многими приложениями, которые отвечают спецификациям OpenGIS.
Реализация хранения пространственной информации SQL Server 2008 основана на этих спецификациях. При этом декларируется совместимость не со всеми стандартами OGC, а с [ Cкачайте файл, чтобы посмотреть ссылку ][ Cкачайте файл, чтобы посмотреть ссылку ] и [ Cкачайте файл, чтобы посмотреть ссылку ].
Пространственная поддержка внутри SQL Server состоит из двух типов данных: geometry и geography.
У каждого пространственного экземпляра имеется идентификатор пространственной ссылки (SRID). Идентификатор SRID соответствует системе пространственных ссылок, основанной на конкретном эллипсоиде, используемом для плоского или сферического сопоставления. Пространственный столбец может содержать объекты с различными идентификаторами SRID. Однако при выполнении операций над собственными данными при помощи методов работы с пространственными данными SQL Server может использовать только пространственные экземпляры с одним и тем же индексом пространственной ссылки SRID.
Результат любого пространственного метода, извлеченный на основе двух экземпляров с пространственными данными, допустим только в случае, если эти экземпляры имеют один и тот же идентификатор SRID, основанный на одних и тех же единице измерения, исходной точке и проекции, использованных для определения координат экземпляров. Наиболее распространенными единицами измерения идентификатора SRID являются метры или квадратные метры.
Если идентификаторы SRID двух пространственных экземпляров различаются, в результате применения к этим экземплярам методов работы с типами данных geometry или geography будет возвращено значение NULL.
Пространственные данные хранятся в двоичном виде внутри SQL Server. При извлечении ASCII-текстового представления пространственных данных используется WKT представление, определенное OGS в разделе 3.2.5 Спецификации простых средств для SQL.

Геометрический тип
WKT-представление
Описание

Point
POINT(10 15)
Точка

Multipoint
MULTIPOINT(10 10, 50 50)
Множество из двух точек

LineString
LINESTRING(10 10, 20 20, 31 35)
Ломаная линия из трёх точек

Polygon
POLYGON((10 10, 10 20, 20 20, 20 15, 10 10))
Многоугольник

GeomCollection
GEOMETRYCOLLECTION(POINT(10 15), LINESTRING(10 10, 20 20))
Коллекция из точки и линии


В пространственных данных SQL Server экземпляр Point является объектом без измерения, представляющим отдельное месторасположение, и может содержать значения Z (уровень) и M (мера).

DECLARE @g geometry;
SET @g = geometry::STGeomFromText('POINT (3 4)', 0);

SET @g = geometry::Parse('POINT(3 4 7 2.5)');

SELECT @g.STX;
SELECT @g.STY;
SELECT @g.Z;
SELECT @g.M;

SET @g = geometry::Parse('POINT(3 4 NULL NULL)');

Экземпляр MultiPoint представляет собой коллекцию точек. Граница у экземпляра MultiPoint отсутствует.

DECLARE @g geometry;
SET @g = geometry::STGeomFromText('MULTIPOINT((2 3),
(7 8 9.5))', 23);

SET @g = geometry::STMPointFromText('MULTIPOINT((2 3),
(7 8 9.5))', 23);

SELECT @g.STGeometryN(1).STAsText();

LineString является одномерным объектом, представляющим последовательность точек и соединяющих их линейных сегментов. Экземпляр LineString должен быть сформирован по меньшей мере двумя различными точками и, кроме того, может быть пустым.
на рисунке 1 представлен простой незамкнутый экземпляр объекта LineString; на рисунке 2 представлен отличный от простого незамкнутый экземпляр объекта LineString; на рисунке 3 продемонстрирован простой замкнутый экземпляр объекта LineString, представляющий собой кольцо; на рисунке 4 продемонстрирован замкнутый непростой экземпляр объекта LineString, не являющийся кольцом.




MultiLineString представляет собой коллекцию экземпляров geometry или geography LineString.
Изображение 1 представляет простой экземпляр MultiLineString , граница которого определяется четырьмя конечными точками двух его элементов LineString. Изображение 2 представляет простой экземпляр MultiLineString, поскольку пересекаются только конечные точки элементов LineString. Граница образована двумя неперекрывающимися конечными точками. Изображение 3 представляет непростой экземпляр MultiLineString, поскольку имеется пересечение внутренней части одного из элементов LineString этого экземпляра. Границей данного экземпляра MultiLineString являются четыре конечные точки. Изображение 4 представляет непростой, незамкнутый экземпляр MultiLineString. Изображение 5 представляет простой, незамкнутый экземпляр MultiLineString. Экземпляр является незамкнутым, поскольку незамкнуты его элементы LineStrings . Экземпляр является простым, поскольку внутренние стороны экземпляров LineStrings не пересекаются. Изображение 6 представляет простой, замкнутый экземпляр MultiLineString. Экземпляр является замкнутым, поскольку все его элементы замкнуты. Экземпляр является простым, поскольку внутренние области его элементов не пересекаются.


Polygon представляет собой двухмерную поверхность, хранимую в виде последовательности точек, определяющих внешнее ограничивающее кольцо, и внутренних колец (последние могут отсутствовать). Каждый экземпляр Polygon должен быть сформирован на основе кольца, содержащего не менее трех уникальных точек. Экземпляр Polygon может быть пустым.
Внешнее и любое внутреннее кольца экземпляра Polygon определяют его границы. Пространство внутри колец определяет внутреннюю сторону экземпляра Polygon. Внутренние кольца экземпляра Polygon могут соприкасаться как сами с собой, так и друг с другом в точках одной касательной, но если внутренние кольца экземпляра Polygon пересекаются, экземпляр недействителен.
на рисунке 1 представлен экземпляр Polygon, граница которого определяется внешним кольцом; на рисунке 2 представлен экземпляр Polygon, граница которого определяется внешним и двумя внутренними кольцами. Область внутри внутренних колец является частью внешней стороны экземпляра Polygon; на рисунке 3 представлен допустимый экземпляр Polygon, поскольку внутренние кольца пересекаются в одной точке касания. Недопустимый экземпляр преобразуется в допустимый с помощью метода MakeValid()



Экземпляр MultiPolygon представляет собой коллекцию экземпляров Polygon.
1 экземпляр типа MultiPolygon с двумя элементами Polygon. Граница определяется двумя внешними кольцами и тремя внутренними кольцами.
2 экземпляр типа MultiPolygon с двумя элементами Polygon. Граница определяется двумя внешними кольцами и тремя внутренними кольцами. Два элемента Polygon пересекаются в точке касания.



Тип данных GeometryCollection представляет собой коллекцию экземпляров geometry или geography. Коллекция GeometryCollection может быть пустой.


DECLARE @g geometry;
SET @g = geometry::STGeomCollFromText('GEOMETRYCOLLECTION(
POINT (3 3 1), POLYGON((0 0 2, 1 10 3, 1 0 4, 0 0 2)))', 1);


Функции работы с GEOMETRY
STAsText
STTouches

STGeometryType
STWithin

STGeomFromText
STDistance

STArea
STUnion

STSrid
GeomFromGML

STIntersect
asGML


STAsText – возвращает представление OpenGIS WKT хранимого значения геометрического типа

STGeometryType – возвращает дружественное имя (т.е. POINT или LINESTRING) хранимого значения геометрического типа

STGeomFromText – Конструирует геометрическое значение для заданного значения в формате WKT

STArea – Возвращает сумму площадей всех поверхностей, определённых в значении геометрического типа

STSrid – возвращает идентификатор пространственной ссылки (SRID) значение, хранимого значения геометрического типа

STIntersect – возвращает true если заданное геометрическое значение пересекает другое геометрическое значение, в противном случае возвращает false

STTouches – возвращает true если заданное геометрическое значение касается другого, в противном случае возвращает false

STWithin – возвращает true если заданное геометрическое находится внутри другого, в противном случае возвращает false

STDistance – возвращает кратчайшее расстояние между точкой заданного геометрического значения и точкой другого значения

STUnion – Возвращает объект, представляющий объединение геометрического значения с другим геометрическим значением

GeomFromGML – конструирует геометрическое значение для заданного значения в формате языка географической разметки (Geometry Markup Language - GML)

asGML – возвращает географическое значение в формате GML



Geography – геодезический тип, описывающий пространственные данные на геодезической плоскости (такой как поверхность Земли) и предоставляющий соответствующие операции над ними. Этот тип данных описывает точки в терминах широты и долготы.

в geometry значения координат в тех же единицах, что и площади, в geography – могут различаться

в geography важна ориентация фигуры

В SQL Server 2008 действуют следующие ограничения на использование типа данных geography.
Любой экземпляр geography должен лежать в пределах одного полушария. Не допускается сохранение пространственных объектов больше размера полушария.
Любой экземпляр geography в представлении консорциума OGC Well-Known Text (WKT) или Well-Known Binary (WKB), порождающий объект больше полушария, приводит к возникновению исключения ArgumentException.
Методы типа данных geography, требующие указания двух экземпляров geography, такие как STIntersection(), STUnion(), STDifference() и STSymDifference(), возвратят NULL, если результаты методов не умещаются в одном полушарии. STBuffer() также возвращает NULL, если выходные данные не умещаются в одном полушарии.

Пространственный индекс определяется для столбца таблицы, в котором содержатся пространственные данные (пространственный столбец). Каждый пространственный индекс относится к ограниченному пространству. Например, индекс для столбца geometry описывает определяемую пользователем прямоугольную область на плоскости.
В SQL Server 2008 пространственные индексы строятся с помощью сбалансированных деревьев, что означает, что индексы должны представлять двумерные пространственные данные в линейном порядке сбалансированных деревьев. Поэтому перед считыванием данных в пространственный индекс SQL Server 2008 проводит иерархическую декомпозицию пространства. В процессе создания индекса происходит декомпозиция пространства в четырехуровневую сеточную иерархию. Эти уровни называют Уровень 1 (верхний), Уровень 2, Уровень 3 и Уровень 4.
Каждый последующий уровень содержит дальнейшую декомпозицию уровня выше, так что каждая ячейка уровня выше содержит полную сетку следующего уровня. На заданном уровне все сетки имеют одинаковое число ячеек на обеих осях (например, 4x4 или 8x8), и все ячейки имеют одинаковый размер.

Ячейки в сеточной иерархии нумеруются в линейном порядке с использованием варианта заполнения пространства кривой Гильберта. Однако на данном рисунке используется простая построковая нумерация вместо нумерации, которая фактически создается кривой Гильберта. На следующем рисунке несколько многоугольников, представляющих здания, и линий, представляющих улицы, помещены в сетку 4x4 уровня 1. Ячейки первого уровня нумеруются от 1 до 16, начиная с верхней левой.
Число ячеек по осям сетки определяет ее плотность: чем больше число, тем плотнее сетка. Например, сетка 8x8 (которая порождает 64 ячейки) плотнее сетки 4x4 (которая порождает 16 ячеек). Плотность сетки определяется по уровням.
Инструкция CREATE SPATIAL INDEX Transact-SQL поддерживает использование предложения GRIDS, которое позволяет указывать различные плотности сетки на разных уровнях. Плотность сетки для данного уровня задается с помощью одного из следующих ключевых слов:
LOW 4X4 16
MEDIUM 8X8 64
HIGH 16X16 256
По умолчанию для всех уровней используется MEDIUM.

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



В 1890 году Джузеппе Пеано открыл плоскую кривую с удивительным свойством "заполнения пространства". Такая кривая заполняла единичный квадрат и проходила через каждую его точку по меньшей мере один раз.
Кривая Пеано основана на разделении каждой стороны единичного квадрата на три равные части, которые делят его на девять меньших квадратов. Кривая проходит эти девять квадратов в определенном порядке. Затем каждый из девяти малых квадратов аналогично делится на девять частей, и кривя модифицируется таким образом, чтобы обойти все части в определенном порядке.
В 1891 году Давид Гильберт открыл вариант кривой Пеано, основанной на делении каждой стороны единичного квадрата на две равные части, что делит квадрат на четыре равные части. Затем каждый из четырех получившихся квадратов, в свою очередь, аналогично делится на четыре меньших квадрата и т.д. На каждой стадии такого деления Гильберт строит кривую, которая обходила все имеющиеся квадраты. Кривая Гильберта представляет собой предельную кривую, полученную в результате такого построения.



После декомпозиции индексированного пространства в cеточную иерархию пространственный индекс считывает данные из пространственного столбца по строкам. После считывания данных для пространственного объекта (или экземпляра) пространственный индекс выполняет процесс тесселяции для этого объекта. Процесс тесселяции помещает объект в cеточную иерархию, устанавливая связь между объектом и набором сеточных ячеек, с которыми он соприкасается (контактные ячейки). Начиная с уровня 1 сеточной иерархии, процесс тесселяции сначала обрабатывает уровень в ширину. В принципе процесс может продолжиться для всех четырех уровней, по одному уровню за раз.
Результатом процесса тесселяции является набор контактных ячеек, которые записываются в пространственный индекс объекта. Ссылаясь на эти записанные ячейки, пространственный индекс может найти объект в пространстве по его расположению относительно других объектов в пространственном столбце, которые также хранятся в индексе.
Если объект полностью покрывает ячейку, говорят, что эта ячейка накрыта объектом. Например, на следующем рисунке одна из ячеек второго уровня (15.11) полностью накрыта средней частью восьмиугольника. Накрытая ячейка считается и записывается в индекс, и дальнейшая тесселяция для ячейки не проводится.




Правило самой глубокой ячейки учитывает тот факт, что каждая ячейка нижнего уровня принадлежит ячейке над ней. Ячейка уровня 4 принадлежит ячейке уровня 3, ячейка уровня 3 принадлежит ячейке уровня 2, а ячейка уровня 2 принадлежит ячейке уровня 1. Например, объект, принадлежащий ячейке 1.1.1.1, также принадлежит ячейке 1.1.1, ячейке 1.1 и ячейке 1. Данные о таких связях между ячейками в иерархии встроены в обработчик запросов. Поэтому в индекс необходимо записывать только ячейки самого нижнего уровня, минимизируя объем данных, хранящихся в индексе.
На следующем рисунке проводится тесселяция относительно небольшого ромбовидного многоугольника. В индексе используется ограничение ячеек на объект, по умолчанию равное 16, которое для этого небольшого объекта не достигается. Поэтому тесселяция проводится вплоть до уровня 4. Многоугольник располагается в следующих ячейках, начиная с уровня 1 и заканчивая уровнем 3: 4, 4.4 и 4.4.10 и 4.4.14. 4.4.10.13-15 и 4.4.14.1-3, 4.4.14.5-7 и 4.4.14.9-11.



Пространственный индекс для столбца geography позволяет соотносить географические данные с двумерным не-евклидовым пространством.

При вычислениях с экземплярами geography (объектами) пространство, содержащее объекты, считается геодезическим эллипсоидом. Для декомпозиции этого пространства схема тесселяции сетки географических объектов разделяет эллипсоид на верхнюю и нижнюю полушария, а затем выполняет следующие шаги.

Проецирует каждое полушарие на грани четырехсторонней пирамиды.
Делает обе пирамиды плоскими.
Соединяет плоские пирамиды для создания не-евклидовой плоскости.
На следующем рисунке показано схематическое представление трехэтапного процесса декомпозиции. В пирамидах пунктирные линии представляют границы четырех граней каждой пирамиды. На шагах 1 и 2 показан геодезический эллипсоид, зеленая горизонтальная линия представляет экваториальную долготу, а ряд зеленых вертикальных линий представляют несколько широт. На шаге 1 показано проецирование двух полушарий на пирамиды. На шаге 2 показано уплощение пирамид. На шаге 3 показаны плоские пирамиды после их объединения в плоскость и число спроецированных линий долготы. Обратите внимание, что эти спроецированные линии выпрямлены и различаются по длине в зависимости от места проецирования на пирамиду.












Робота з датою та географічною інформацією (server 2008 only)
FILESTREAM
2. Date, time, datetime, date, time, timestamp, DATETIMEOFFSET, dateadd, datediff,datepart, datename
3. Географические данные: GEOMETRY + GEOGRAPHY
Малювання полігонів, визначення приналежності точки до регіону, обчислення відстані
між регіонами тощо [[FULL => see training 2008]]


Список літератури

Dan Wood, Chris Leiter, Paul Turley. Beginning SQL Server 2005 Administration, Wiley Publishing, 2007.
· 603 p.
Dusan Petkovic. Microsoft SQL Server 2008 A Beginner’s Guide, McGraw-Hill, 2008.
· 709 p.
Itzik Ben-gan. Microsoft SQL Server 2008 T-SQL Fundamentals, Microsoft Press, 2008.
· 688 p.
Joseph Sack. SQL Server 2008 Transact-SQL Recipes: A Problem-Solution Approach, Apress, 2008.
· 872 p.
Louis Davidson, Kevin Kline, Kurt Windisch. Pro SQL Server 2005 Database Design and Optimization, Apress, 2006.
· 644 p.
Louis Davidson, Kevin Kline, Scott Klein, Kurt Windisch. Pro SQL Server 2008 Relational Design and Implementation, Apress, 2008.
· 680 p.
Paul Nielsen. SQL Server 2005 Bible, Wiley Publishing, 2007.
· 1293 p.
Peter DeBetta, Greg Low, Mark Whitehorn. Introducing Microsoft SQL Server 2008, Microsoft Press, 2008.
· 237 p.
Robert Vieira. Beginning Microsoft SQL Server 2008 Programming, Wrox, 2009.
· 720 p.
Robin Dewson. Beginning SQL Server 2008 for Developers: From Novice to Professional, Apress, 2008, 496 p.
SQL Server 2008 Books Online [ Cкачайте файл, чтобы посмотреть ссылку ].
Роберт Э. Уолтерс, Майкл Коулс, Роберт Рей, Фабио Феррачати, Дональд Фармер. SQL Server 2008: ускоренный курс для профессионалов, Вильямс, 2008.
· 768 c.
[ Cкачайте файл, чтобы посмотреть ссылку ][ Cкачайте файл, чтобы посмотреть ссылку ][ Cкачайте файл, чтобы посмотреть ссылку ][ Cкачайте файл, чтобы посмотреть ссылку ][ Cкачайте файл, чтобы посмотреть ссылку ][ Cкачайте файл, чтобы посмотреть ссылку ][ Cкачайте файл, чтобы посмотреть ссылку ][ Cкачайте файл, чтобы посмотреть ссылку ][ Cкачайте файл, чтобы посмотреть ссылку ][ Cкачайте файл, чтобы посмотреть ссылку ][ Cкачайте файл, чтобы посмотреть ссылку ][ Cкачайте файл, чтобы посмотреть ссылку ][ Cкачайте файл, чтобы посмотреть ссылку ]
[ Cкачайте файл, чтобы посмотреть ссылку ][ Cкачайте файл, чтобы посмотреть ссылку ][ Cкачайте файл, чтобы посмотреть ссылку ][ Cкачайте файл, чтобы посмотреть ссылку ][ Cкачайте файл, чтобы посмотреть ссылку ][ Cкачайте файл, чтобы посмотреть ссылку ][ Cкачайте файл, чтобы посмотреть ссылку ][ Cкачайте файл, чтобы посмотреть ссылку ][ Cкачайте файл, чтобы посмотреть ссылку ][ Cкачайте файл, чтобы посмотреть ссылку ][ Cкачайте файл, чтобы посмотреть ссылку ][ Cкачайте файл, чтобы посмотреть ссылку ][ Cкачайте файл, чтобы посмотреть ссылку ][ Cкачайте файл, чтобы посмотреть ссылку ][ Cкачайте файл, чтобы посмотреть ссылку ]
[ Cкачайте файл, чтобы посмотреть ссылку ][ Cкачайте файл, чтобы посмотреть ссылку ][ Cкачайте файл, чтобы посмотреть ссылку ][ Cкачайте файл, чтобы посмотреть ссылку ][ Cкачайте файл, чтобы посмотреть ссылку ][ Cкачайте файл, чтобы посмотреть ссылку ][ Cкачайте файл, чтобы посмотреть ссылку ][ Cкачайте файл, чтобы посмотреть ссылку ][ Cкачайте файл, чтобы посмотреть ссылку ][ Cкачайте файл, чтобы посмотреть ссылку ][ Cкачайте файл, чтобы посмотреть ссылку ][ Cкачайте файл, чтобы посмотреть ссылку ][ Cкачайте файл, чтобы посмотреть ссылку ][ Cкачайте файл, чтобы посмотреть ссылку ][ Cкачайте файл, чтобы посмотреть ссылку ][ Cкачайте файл, чтобы посмотреть ссылку ][ Cкачайте файл, чтобы посмотреть ссылку ][ Cкачайте файл, чтобы посмотреть ссылку ]
[ Cкачайте файл, чтобы посмотреть ссылку ][ Cкачайте файл, чтобы посмотреть ссылку ]
[ Cкачайте файл, чтобы посмотреть ссылку ]
geometry [ Cкачайте файл, чтобы посмотреть ссылку ]
geography [ Cкачайте файл, чтобы посмотреть ссылку ][ Cкачайте файл, чтобы посмотреть ссылку ]
Методы OGC в экземплярах Geometry [ Cкачайте файл, чтобы посмотреть ссылку ]
Расширенные методы экземпляров Geometry [ Cкачайте файл, чтобы посмотреть ссылку ]
Статические геометрические методы OGC [ Cкачайте файл, чтобы посмотреть ссылку ]
Расширенные статические геометрические методы http://msdn.microsoft.com/ru-ru/library/bb933805.aspx









13PAGE 15


13PAGE 141115






















http://msdn.microsoft.com/en-us/library /ms130214.aspxhttp://msdn.microsoft.com/en-us/library /ms130214.aspx Heading 1 Heading 2 Heading 3 Heading 4 Heading 5 Heading 6 Heading 7 Heading 8 Heading 9Default Paragraph Font Table Normal
No List Body TextBody Text Indent Plain TextBody Text Indent 2Body Text Indent 3 Page Number Normal (Web) Body Text 2 HTML CodeTOC 1TOC 3HTML Preformatted

Приложенные файлы

  • doc 7768756
    Размер файла: 487 kB Загрузок: 0

Добавить комментарий