sql date range select

Question

hi,

how can i select data using date range


eg; select * from table where date>=?date and date1<=?date

               any wrong with the above? please do some correction
               i just wanted to select records in specified range of date
              please help


young fox

Answer 1

See if this article helps you http://www.databasejournal.com/features/mssql/article.php/10894_2209321_2/Working-with-SQL-Server-DateTime-Variables-Part-Three---Searching-for-Particular-Date-Values-and-Ranges.htm

---

Thanks, Leks

Answer 2

Young fox,

Datetime ranges are even tricky for old foxes. SQL Server 2008 comes to the rescue with the date data type.

See the demo following for datetime. It is very important that you code your range that way, otherwise the query results will be invalid.

-- Date range FEB 2004
SELECT OrderCount=COUNT(*)
FROM AdventureWorks2008.Sales.SalesOrderHeader
WHERE OrderDate >='2004-02-01'
  AND OrderDate < '2004-03-01'
-- 2032
  

---

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

Answer 3

Hi,

You can also use between keyword

Select * from table where date between @startdate and @enddate

Thanks
Gnanasekar Babu

---

Thanks, Gnanasekar Babu Note: Please click on the vote button if the answer helps you

Answer 4

Select * from table where date between @startdate and @enddate

The BETWEEN operator may lead to a very common mistake with datetime.

declare @startdate datetime = '2010-01-01'
declare @enddate datetime = '2010-01-31'
Select * from table where date between @startdate and @enddate

The above range query would not include any data for 2010-01-31 starting after midnight like 2010-01-31 00:01:00 .

Article: http://www.sqlusa.com/bestpractices2008/between-dates/

---

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

Answer 5

You are very true Kalman , I have had so many issue with this kind of select between certain dates.

---

Thanks, Leks

Answer 6

To the rescue: date data type in SQL Server 2008 T-SQL. BETWEEN is ok to use with date columns.

---

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com

Answer 7

Hi Young Fox,



U r perfectly right.....Just u need to specify the date(s) in sigle quotes....enough

Cheers,

Sridhar

Answer 8

I have an elaboration of the topic here: http://www.karaszi.com/SQLServer/info_datetime.asp

---

Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi

Answer 9

Tibor,

In your experience what is the most frequent mistake database developers make with datetime ranges?

Thanks.

---

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com

Answer 10

hello guys,

i thank you all for lots of learning from you, all of you contributed very well. your replies lead me to a result on my app.
god bless


young fox

Answer 11

Kalman,

By far, IMO the most common mistake generally for datetime would be using formats which aren't language independent.

As for searches, no 1 would be not taking time portion into account. Number two would be using BETWEEN.

---

Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi