Technical Article

datetime precision

,

There is a problem with storing too much precision in a column. For example, storing $12.345 as money does not make sense in the U.S. since we only use two decimals for cents. The same problem occurs with datetimes and smalldatetimes. We sometimes store time with a date when, for example, a price is always effective for whole days only. Encountering time data in that database column will completely confuse developers and introduce bugs when they write sql for it.

I've included a function that trims a getdate() value to whatever precision a table's column should store. You can use the function in a rule on that column too, to guarantee a higher precision is not stored. It would be nice if Microsoft considered adding a precision to the datetime type declaration as they do for decimal(precision,scale).

 

 

create function  dbo.SmallDateTimePrecision (@sdt smalldatetime, @precision varchar(7))
returns smalldatetime
as
-- written by Bill Talada
begin
declare
@filler varchar(16),
@length int,
@fixed smalldatetime

set @filler = 'cc00-01-01 00:00'
--'ccyy-mm-dd hh:mm'

set @length =
case @precision
when 'century' then 2
when 'year' then 4
when 'month' then 7
when 'day' then 10
when 'hour' then 13
else 0
end

if @length = 0 
set @fixed = @sdt
else
set @fixed = cast(left(convert(varchar(16), @sdt, 121),@length) + right(@filler,16-@length) as datetime)

return @fixed
end
GO

--Precision in smalldatetime
set nocount on
declare @sdt smalldatetime 
set @sdt = getdate()
select
@sdt,
dbo.SmallDateTimePrecision(@sdt, 'Century'),
dbo.SmallDateTimePrecision(@sdt, 'Year'),
dbo.SmallDateTimePrecision(@sdt, 'Month'),
dbo.SmallDateTimePrecision(@sdt, 'Day'),
dbo.SmallDateTimePrecision(@sdt, 'Hour'),
dbo.SmallDateTimePrecision(@sdt, 'Minute') 
GO

/* -- results
@sdt2009-12-21 09:56:00
Century2000-01-01 00:00:00
Year2009-01-01 00:00:00
Month2009-12-01 00:00:00
Day 2009-12-21 00:00:00
Hour2009-12-21 09:00:00
Minute2009-12-21 09:56:00
*/

Rate

3.5 (2)

Share

Share

Rate

3.5 (2)