Searching Date Range - Lost the plot.
-
I'd be grateful if someone could help me out with this query.
I want to do a search on a table based on the submission date of a record. The SubDate field has a datatype of datetime. This is my syntax:
SELECT *
FROM MyTable
WHERE CONVERT(datetime, SubDate,103) BETWEEN '01/03/2005' AND '31/03/2005'
This does return a range of records including and between those two dates, although it doesn't return all of them!
If I do this query I get all of the records:
SELECT *
FROM MyTable
WHERE SubDate BETWEEN '01/03/2005 00:00:00' AND '31/03/2005 23:59:59'
Where am I going wrong?
I'd be grateful for any assistance.
Thanks,
Kevin.
Windows 2008 Server | SQL Server 2008
-
BETWEEN '01/03/2005' AND '31/03/2005'
is the same as doing
BETWEEN '01/03/2005' AND '31/03/2005 00:00:00'. That's why you are missing all the records from that day.
BTW your query will run faster if you can avoid doing where convert() and using where Datecol between...
-
Also, see if this provides additional information:
http://www.sql-server-performance.com/fk_datetime.asp
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
SELECT *
FROM MyTable
WHERE SubDate BETWEEN '01/03/2005 00:00:00' AND '31/03/2005 23:59:59'
Even this statement will miss rows with a date of 31/03/2005 23:59:59 containing milliseconds > 000.
For a excellent understanding of how to work with sql server datetime you should take a look at the article mentioned above by Frank Kalis.
-
I like use datetime functions, For example:
SELECT *
FROM MyTable
WHERE day(SubDate) between 1 and 31
and month(SubDate)=3
and year(SubDate)=2005
Hope it helpful.
S.
-
That's nice but that will ALWAYS result in a scan of the table or index. You'll never get any real performance on larger tables... and it's gonna be a nightmare on huge tables with Ms of rows.
-
Sliu,
Remi is correct. If you are still using such an approach to dates then do yourself a big favor and read the article mentioned above by Frank Kalis.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply