datetime precision

William Talada, 2010-01-20 (first published: 2009-12-21)

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 */