Microsoft SQL Server
Transact-SQL

Работа с датой в MSSQL

Опубликовано: 27 сен 02
Рейтинг:

Автор: Constantine Ed. Koulakov - cat2
Прислал: judge

DateTime
Все примеры проверялись на MS SQL 7.0
0. Представление DateTime в MS SQL
Выдержка из BOL

datetime

Date and time data from January 1, 1753, to December 31, 9999, with an accuracy of three-hundredths of a second, or 3.33 milliseconds.

Эксперимент №1

select substring(name,1,20) as Name,length, xprec, xscale 
from systypes where name in ('datetime','money')
Name                 length xprec xscale 
-------------------- ------ ----- ------ 

datetime             8      23    3
money                8      19    4
Вывод. Это похоже на число с фиксированной зяпятой.

Эксперимент №2
set nocount on
select convert(datetime,'2002-12-31 00:00:00.000')+1
select convert(datetime,'2002-12-31 00:00:00.000')+0.1
select convert(datetime,'2002-12-31 00:00:00.000')+0.01
select convert(datetime,'2002-12-31 00:00:00.000')+0.001
select convert(datetime,'2002-12-31 00:00:00.000')+0.0001
select convert(datetime,'2002-12-31 00:00:00.000')+0.00001
select convert(datetime,'2002-12-31 00:00:00.000')+0.000001
select convert(datetime,'2002-12-31 00:00:00.000')+0.0000001
select convert(datetime,'2002-12-31 00:00:00.000')+0.00000001
--------------------------- 

2003-01-01 00:00:00.000
2002-12-31 02:24:00.000
2002-12-31 00:14:24.000
2002-12-31 00:01:26.400
2002-12-31 00:00:08.640
2002-12-31 00:00:00.863
2002-12-31 00:00:00.083
2002-12-31 00:00:00.007
2002-12-31 00:00:00.000


select 0,convert(datetime,'2002-12-31 00:00:00.000')+0.00000010
select 1,convert(datetime,'2002-12-31 00:00:00.000')+0.00000011
select 2,convert(datetime,'2002-12-31 00:00:00.000')+0.00000012
select 3,convert(datetime,'2002-12-31 00:00:00.000')+0.00000013
select 4,convert(datetime,'2002-12-31 00:00:00.000')+0.00000014
select 5,convert(datetime,'2002-12-31 00:00:00.000')+0.00000015
select 6,convert(datetime,'2002-12-31 00:00:00.000')+0.00000016
select 7,convert(datetime,'2002-12-31 00:00:00.000')+0.00000017
select 8,convert(datetime,'2002-12-31 00:00:00.000')+0.00000018
select 9,convert(datetime,'2002-12-31 00:00:00.000')+0.00000019
----------- --------------------------- 

0           2002-12-31 00:00:00.007
1           2002-12-31 00:00:00.007
2           2002-12-31 00:00:00.010
3           2002-12-31 00:00:00.010
4           2002-12-31 00:00:00.010
5           2002-12-31 00:00:00.010
6           2002-12-31 00:00:00.013
7           2002-12-31 00:00:00.013
8           2002-12-31 00:00:00.013
9           2002-12-31 00:00:00.013
Вывод. Целая часть DateTime Представляет из себя количество суток. Дробная часть имеет длину в 8 знаков. 3.33 милисекунды из BOL соответсвуют 0.00000002.
1. Вопрос. Мой клиент не правильно передает значение DateTimе серверу.
Всегда используйте передачу значения DateTime в виде строки.

Подготовленное для передачи значение должно быть представлено
в формате ISO - 'yyyymmdd hh:mm:ss.mmm'

2. Вопрос. Значение в хранимую процедуру передано правильно, в формате ISO, внутри процедуры привожу его к DateTime и после этого процедура обрабатывет его неверно.
Внутри хранимой процедуры не преобразовывайте переданный параметр в DateTime, продолжайте использовать его в виде строки.

Например
create procedure proc dt char(21) as
begin transaction
update table1 
set DateTimeField=dt

update table2 
set IntField=0
where DateTimeField=dt
commit transaction
3. Вопрос. В моей таблице не надо хранить данные о часах и минутах.
Если значение DateTime передает клиент, то передавайте его в виде строки 'yyyymmdd'.

Для Дельфи это может выглядеть так:
ADODataSet.CommandText:='update table1 set DateTimeField='+
ansiQuotedStr(FormatDateTime('yyyymmdd',MyDateTime),'''')
Если присвоение поля DateTime присходит внутри запроса, то отсечь значения часов и минут можно так:
declare @dt DateTime
select @dt=convert(char(8),getdate(),112)
4. Вопрос. При работе с таблицами Paradox для выборки значений по нужному периоду я использовал сравнение через LIKE. После перехода на SQL, мои запросы стали работать некорректно.
Не надо применять LIKE.

Отбор требуемого периода следует делать делать через сравнение дат.

Примеры для отбора значений за январь 2002 года

а) В поле не хранятся значения часов и минут

select * from table1
where DateTimeField between '20020101' and '20020131'
b) В поле хрянятся значения часов и минут
select * from table1
where DateTimeField>='20020101' and DateTimeField< '20020201'
Запрос b) сработает и если в поле НЕ хранятся значения часов и минут.

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

а) Значение не участвует в расчетах, а служит только для "красоты" вывода.

select convert(char(8),getdate(),114),convert(char(12),getdate(),114)
-------- ------------ 

19:59:27 19:59:27:997
б) Значение участвует в расчетах, например для определения интервала между событиями.

declare @dt0 DateTime,@dt1 DateTime
select @dt0='20020101'
select @dt1='20020101 8:50:00'
select datediff(hour,@dt0,@dt1),datediff(minute,@dt0,@dt1)
----------- ----------- 

8           530
[src]
Если значение служит для хранения данных о продолжительности какого-то процесса, то лучше использовать числовое представление в часах или других единицах,в зависимости от требуемой точности. 

6. Мне нужно сделать группировку по месяцам
[src]use NorthWind
Select convert(char(6),OrderDate,112), count(ShipCountry) from Orders
group by convert(char(6),OrderDate,112) 
order by 1
А по кварталам?

Select datepart(year,OrderDate), datepart(quarter,OrderDate),
count(ShipCountry) from Orders
group by datepart(year,OrderDate), datepart(quarter,OrderDate) 
order by 1,2

--------------------------------------------------------------------------------
20020816. Constantine Ed. Koulakov - cat2

Комментарии




Необходимо войти на сайт, чтобы оставлять комментарии

Раздел FAQ: Microsoft SQL Server / Transact-SQL / Работа с датой в MSSQL