Seems like this comes up a lot so I thought I’d blog about a clean way I found to do this from a StackOverflow article. Basically, the problem is if you have a bunch of Sql records that have date and times in them (not 00:00:00 for time), using Microsoft’s SqlServer. You may want to query all records that are on a certain date. You can certainly for a query that looks like:
SELECT * FROM VolunteerJobs
WHERE JobStartTime >='08-01-2008' AND
JobStartTime<='08-14-2008 23:59:59.996'
However, this feels a little awkward. A better way that I plan on using is:
SELECT *
FROM VolunteerJob
WHERE DateAdd
(day, datediff(day, 0, JobStartTime), 0) =
'2010-10-09'
Somehow, it just feels better. HTH’s.
Peter is a software professional specializing in mobile and web technologies. He has also been a Microsoft MVP for the past 7 years. To read more about Peter Kellner and his experience click here. For information about how Peter Kellner might be able to help you with your project click here.
Follow me:
Just my 2 cents…
Out of the three, the first query is the quickest when properly indexed. Your proposed is the slowest and requires a table or index scan (which is usually terrible). Milligan’s is likely the best since there is a slight overhead for the scalar computation, but is the cleanest to read and program.
or…
select * from volunteerjob
where cast(jobstarttime as date) = ’10/9/2010′
(sql 2008+)