The pain of SQL Server and working with dates

How many times have you tried to design an efficient database table design in SQL Server only to stumped with the problem of storing pure date values. Yes the currently released versions of SQL Server (up to 2005) don’t support a pure date only data type. Note that SQL Server 2008 add the new DATE data type which should make this post semi-redundant. This can prove painful when you wanting to store values for a specific day. So you end up getting creative. My normal solution is to put a check constraint on the column to enforce that the date value represents the start of the day or 00h00. Oh and I generally make sure I store all my date and time values in UTC so I can render to any time-zone that my application desires.

Here is an example of how I’d do a check constraint on column UtcDate. Note how I effectively truncate the floating-point representation of the date and time value and check that it equals the column value. Also I store it as a SMALLDATETIME as I’m not needing more than 1 minute accuracy. Oh and I’ve tried to reformat the examples to be easier to read on the web, I hope it works. 🙂 

CREATE TABLE [dbo].[Foo]
(
 [UtcDate] SMALLDATETIME NOT NULL
            CONSTRAINT [CK_Foo_UtcDate]
             CHECK (FLOOR(CONVERT(FLOAT,[UtcDate]))=CONVERT(FLOAT,[UtcDate]))
) ON [PRIMARY]
GO

This method works great for me but in searching for a way to truncate the minutes of a DATETIME to a specific interval I found another approach that not only looks more elegant but most likely performs a lot better. I haven’t run any proper performance tests so I’d appreciate some feedback from those that have done some testing.

CREATE TABLE [dbo].[Foo]
(
 [UtcDate] SMALLDATETIME NOT NULL
            CONSTRAINT [CK_Foo_UtcDate]
             CHECK (DATEADD(DAY,DATEDIFF(DAY,0,[UtcDate]),0)=[UtcDate])
) ON [PRIMARY]
GO

It looks a lot more efficient than my previous approach, hopefully it is. Oh and take a look here for more very useful tips and tricks when working with dates and times in SQL Server.

[tags]SQL,SQL Server[/tags]