A Quick Tip On How To Ignore Time When Querying From SqlServer

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.

About Peter Kellner

Peter is a software professional specializing in mobile and web technologies. He has also been a Microsoft MVP since 2007. In addition, he's a multi-course video author at Pluralsight. 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:


Comments

  1. Michael Emerick says:

    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.

  2. or…
    select * from volunteerjob
    where cast(jobstarttime as date) = ’10/9/2010′

    (sql 2008+)

Follow

Get every new post delivered to your Inbox

Join other followers: