Update: More on dates and times in T-SQL here.

I don't like storing dates along with the time portion in my database tables. 

Sure, I use the datatype, but I wish SQL provided a “Date” datatype and a “Time” datatype that were separate. It really sometimes requires too much work in T-SQL to separate the date portion from the time portion, and often in a WHERE clasue you don't care about the time, you just want data for a single day.  A common question asked at SQLTeam is:

 Why doesn't the condition WHERE DateTimeColumn =  '1/1/2004' return any results?  I can see there is definitely data in that table for Jan 1st, 2004 !

 The answer, of course, is because “DateTimeColumn” contains a time portion as well.  Some solutions that are given often look like this:

A) WHERE DateDiff(dd, DateTimeColumn, '1/1/2004') = 0

B) WHERE Convert(varchar(20), DateTimeColumn, 101) = '01/01/2004'

C) WHERE DateTimeColumn LIKE '1/1/2004%'

D) WHERE Year(DateTimeColumn) = 2004 AND Month(DateTimeColumn) = 1 and Day(DateTimeColumn)=1

I don't really like any of those, to be honest.  Many rely on system settings for date formats.  Others rely on conversions to varchars.  None are able to make use of any indexes on the DateTimeColumn so they will not be as efficient as possible. 

The best solution in terms of performance isn't even really that good, because it requires a range even though we wish to return data just for a particular day:

E) WHERE DateTimeColumn >= '1/1/2004'  AND DateTimeColumn < '1/2/2004'

If you encounter this situation often, here's an idea: 

Don't store the time with the date in your database!  Keep them in separate columns.  You can always combine them extremely easily just by adding them together to recreate your original DateTime value.  But for all those other times when you wish to just filter for a particular day, you can use a quick and efficient = or BETWEEN comparison on the date only.

To do this, you need to :

1) Create two columns instead of one -- a Date column and a Time column

2) Enforce that the Date column never has a time stored in it:

 DateCol datetime check (dateAdd(dd,datediff(dd,0,DateCol),0) = DateCol)

3) Enforce that the Time column never has a date portion that is a date other than the “base date” (which is the datetime that the numeric value 0 converts to):
 
 TimeCol datetime check (datediff(dd,0,TimeCol) = 0))

Once that is done, any time you wish to return the Date along with the Time, you can just add them together!  That's right, just SELECT DateCol + TimeCol as DateTimeVal FROM YourTable.

The key here is now you can index your DateCol and filter off of it directly, without worrying about conversions or time issues, which results in efficient execution plans.  With the constraints, you are guaranteed that only dates are stored there, so you never have to worry about it.  And, you can effortlessly retrieve the original DateTime value with a simple addition of the two columns (no conversions needed!).

If you like, you can create a View of your table and have that view return your DateTime column as well as the Date and the Time broken out if you like.  You can even allow for this View to be updateable so that client applications don't have to worry about manually breaking out the Date and the Time themselves.  See the code below for an example of this trigger, along with the constraints and some sample data. 

A couple of notes:

1) This does, of course, require more memory since you now require twice as much storage for your DateTime values. 

2) If, on occasion, you do wish to SELECT from this table and filter by a specific date and time range, then  the view will not be especially efficient if you simply use:

 WHERE DateTimeCol BETWEEN @StartDateTime AND @EndDateTime

This is because now DateTimeCol is now a formula. You can either make use of Indexed Views to index this column, or you can use what I call an efficiently redundant WHERE clause like this:

 WHERE DateCol BETWEEN (@StartDateTime-1) AND (@EndDateTime+1) AND
      DateTimeCol BETWEEN @StartDateTime AND @EndDateTime

That will allow SQL Server to use the index on the DateCol for the initial filter, and then from there, the second part of the WHERE clause will have many fewer rows to scan.

see also:

• Working with Time Spans and Durations in SQL Server
• Group by Month (and other time periods)
• Working with Date and/or Time values in SQL Server: Don't Format, Don't Convert -- just use DATETIME
• Data Types -- The Easiest Part of Database Design
• How to format a Date or DateTime in SQL Server
• Breaking apart the DateTime datatype -- Separating Dates from Times in your Tables
• Date Only and Time Only data types in SQL Server 2005 (without the CLR)
• Essential SQL Server Date, Time and DateTime Functions