SQL Server – Get data between two dates

This is the SQL to get data between two dates, I know lots of people find datediff() confusing so this is a nice cut and paste script to have handy:

select * from
tableName
where datediff(day,'2011-06-27 12:28:34.480',theDate)>=0 and
datediff(day,'2011-06-28 12:28:34.480',theDate)<=0

The first date in that string is the start date and the second is the end date. You can change the ‘day’ parameter to any amount you wish (second, month, year). You should always use datediff() to get the difference between dates in SQL Server as it will handle issues like 30, 31 and 28 day months, leap years, etc. datediff() is odd in that if you run this:

select datediff(day,'2011-06-27 12:28:34.480','2011-06-28 12:28:34.480')

It will give you a whole number, in this case 1. So when the left side is less than the right side we get positive numbers. This seems backwards to the more natural seeming syntax of greater than / less than:
select * where date1 > date2
Unfortunately you can’t use greater than / less than, this is because even though they work, they will compare down to the nanosecond, which is not generally what we want.

If you need to do this in your .Net dataset here are two examples of how to do it. Two because the way you write your query will vary depending on what database driver you’re using.

For SQL Server 2000:

select * from
tableName
where datediff(day,?,theDate)>=0 and
datediff(day,?,theDate)<=0

For SQL Server 2005+:

select * from
tableName
where datediff(day,@StartDate,theDate)>=0 and
datediff(day,@EndDate,theDate)<=0

Remember that if you have issues the default way to use this is with question (?) marks. Question marks were used to denote parameters all the way back to ADO in VB 6. The downside of using question marks is your parameters must be added to your code in the order they are in the query. (In Oracle they always have to be added in the order of parameters in the query, despite being named. Unless they’ve fixed that since I last used the Oracle driver).

Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: