Click here to Skip to main content
12,503,238 members (62,460 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

13.4K views
10 bookmarked
Posted

Accurate DATETIME Range Searching in SQL Server

, 9 Dec 2009 CPOL
Rate this:
Please Sign up or sign in to vote.
When searching between datetimes in SQL(or SQL in a Stored Proc) we can't just do:select * from where CreateTime >= @DateFrom and CreateTime <= @DateToIf your search is date inclusive and you're not concerned with time, which most users aren't, then you need to add a little extra...
When searching between datetimes in SQL(or SQL in a Stored Proc) we can't just do:
select * from <table>
where CreateTime >= @DateFrom and CreateTime <= @DateTo
If your search is date inclusive and you're not concerned with time, which most users aren't, then you need to add a little extra code to get the right results. Basically, you need to add one day to @DateTo and trunc. Here's a Sql Server 2005 function that will help:

CREATE FUNCTION [dbo].[trunc]
(
    @dt datetime
)
RETURNS datetime
AS
BEGIN
            return dateadd(day, datediff(day, 0, @dt), 0);
END
Now your SQL should be:
select * from <table>
where CreateTime >= dbo.trunc(@DateFrom)
and CreateTime < dbo.trunc(@DateTo + 1)
If you want more info about this then go to: Accurate DATETIME Range Searching in SQL Server[^]

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Mark Graham
Web Developer Currently working on projects for May Gurney
United Kingdom United Kingdom
Areas of expertise: c#, asp.net, design patterns, GUI useability/layout

You may also be interested in...

Pro
Pro

Comments and Discussions

 
GeneralMy vote of 4 Pin
source.compiler23-Jul-12 17:54
membersource.compiler23-Jul-12 17:54 
QuestionWhat about... Pin
Peter Tewkesbury4-Dec-09 8:51
memberPeter Tewkesbury4-Dec-09 8:51 
AnswerRe: What about... Pin
Mark Graham7-Dec-09 0:18
memberMark Graham7-Dec-09 0:18 
GeneralRe: What about... Pin
supercat99-Dec-09 5:41
membersupercat99-Dec-09 5:41 
Mark Graham wrote:
The tip is more about awareness, but, absolutely, and I've no doubt this will be quicker.


I prefer using a "less-than" condition with the next day, since otherwise one must make some assumption about the precision of a date time object. If a date-time object is good for tenths of a second, for example, one would have to force the time portion to to "23:59:59.9" to avoid having times which fall through the cracks.

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.160919.1 | Last Updated 9 Dec 2009
Article Copyright 2009 by Mark Graham
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid