January 1, 2009 at 8:18 pm
I have a datetime column with an example value of: 1/1/2008 10:30:59
I would like to filter on the dates of January and February of 2007, with the time being between 8PM and 9PM. What would the SQL be?
Thanks...Nali
January 1, 2009 at 8:55 pm
I assume this is what you want
Select col1,col2... from table1 where col2 between 2007/01/01 12:00:00:000' AND '2007/02/28 23:59:59:997' .
"Keep Trying"
January 1, 2009 at 8:57 pm
Not quite, I only want the values between 8PM and 9PM for all of January and February...
January 1, 2009 at 9:09 pm
I think you need something like this
select * from tablename where datepart(yy , dateColumn) = 2007
and datepart(mm,dateColumn) <= 2
and datepart(hh, dateColumn) between 20 and 21
🙂
:w00t:
January 2, 2009 at 1:15 am
though i liked the solution provided in the previous post, here is another version..
select * from mytable where convert(varchar(6),datecolumn,112) between '200701' and '200702'
and convert(varchar(2), datecolumn, 108) between '20' and '21'
January 2, 2009 at 1:45 am
When you use function in the where clause on the column, the server can’t use any index that was created on that column. Although the queries that were introduced by other will work, they will not use an index on DateColumn (assuming that one exists and can improve the performance). In order to have a chance that an index will be used, I would use the dates in the query instead of using functions that will give me parts of the dates. My version of the query will be:
select * from tablename
where dateColumn >= '20070101' and dateColumn < '20070301'
and datepart(hh, dateColumn) = 20
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 2, 2009 at 3:41 am
Adi Cohn (1/2/2009)
When you use function in the where clause on the column, the server can’t use any index that was created on that column. Although the queries that were introduced by other will work, they will not use an index on DateColumn (assuming that one exists and can improve the performance). In order to have a chance that an index will be used, I would use the dates in the query instead of using functions that will give me parts of the dates. My version of the query will be:select * from tablename
where dateColumn >= '20070101' and dateColumn < '20070301'
and datepart(hh, dateColumn) = 20
Adi
It's always nice to have some proof...and here it is...
[font="Courier New"]-- Some code nicked from Jeff Moden's excellent tally table article
-- to make up sample dates
--===== Presets
DECLARE @DateStart DATETIME
DECLARE @DateEnd DATETIME
SELECT @DateStart = '1998-01-01 06:00',
@DateEnd = DATEADD(yy,15,@DateStart)
--===== Display the shift number and date/times
DROP TABLE #Dates
SELECT DATEADD(mi,10*(t.number ),@DateStart) AS ShiftEnd
INTO #Dates -- (788977 row(s) affected)
FROM dbo.Numbers t
WHERE DATEADD(mi,10*(t.number-1),@DateStart) <= @DateEnd
-- run with / without index for comparison
CREATE INDEX [ShiftEnd] ON #Dates([ShiftEnd]) ON [PRIMARY]
------------------------------------------------------------------------
DECLARE @DateRangeStart DATETIME, @DateRangeEnd DATETIME
SET @DateRangeStart = '2007-01-01 00:00'
SET @DateRangeEnd = '2007-03-01 00:00'
SET STATISTICS TIME ON
SET STATISTICS io ON
---------------------------------------------------------------------
-- Adi's version
SELECT COUNT(*)
FROM #Dates
WHERE ShiftEnd >= @DateRangeStart AND ShiftEnd < @DateRangeEnd
AND DATEPART(hh, ShiftEnd) = 20
-- 354 rows
-- WITHOUT INDEX CPU time = 156 ms, elapsed time = 167 ms.
-- WITH INDEX CPU time = 0 ms, elapsed time = 2 ms. logical reads 26
---------------------------------------------------------------------
-- vishal
SELECT COUNT(*)
FROM #Dates
WHERE DATEPART(yy , ShiftEnd) = 2007
AND DATEPART(mm,ShiftEnd) <= 2
AND DATEPART(hh, ShiftEnd) BETWEEN 20 AND 21
-- (708 rows)
-- WITHOUT INDEX CPU time = 214 ms, elapsed time = 214 ms.
-- WITH INDEX CPU time = 213 ms, elapsed time = 213 ms. logical reads 2153
-- vishal corrected
SELECT COUNT(*)
FROM #Dates
WHERE DATEPART(yy , ShiftEnd) = 2007
AND DATEPART(mm,ShiftEnd) <= 2
AND DATEPART(hh, ShiftEnd) = 20
-- (354 rows)
-- WITHOUT INDEX CPU time = 212 ms, elapsed time = 212 ms.
-- WITH INDEX CPU time = 219 ms, elapsed time = 224 ms. logical reads 2153
---------------------------------------------------------------------------
-- ps
SELECT COUNT(*)
FROM #Dates
WHERE CONVERT(VARCHAR(6),ShiftEnd,112) BETWEEN '200701' AND '200702'
AND CONVERT(VARCHAR(2), ShiftEnd, 108) BETWEEN '20' AND '21'
-- (708 rows)
-- WITHOUT INDEX CPU time = 2172 ms, elapsed time = 2203 ms.
-- WITH INDEX CPU time = 2578 ms, elapsed time = 2583 ms. logical reads 2153
-- ps corrected
SELECT COUNT(*)
FROM #Dates
WHERE CONVERT(VARCHAR(6),ShiftEnd,112) BETWEEN '200701' AND '200702'
AND CONVERT(VARCHAR(2), ShiftEnd, 108) = '20'
-- (354 rows)
-- WITHOUT INDEX CPU time = 2172 ms, elapsed time = 2172 ms.
-- WITH INDEX CPU time = 1453 ms, elapsed time = 1460 ms. logical reads 2153
-----------------------------------------------------------------------------
-- ChrisM version
SELECT COUNT(*)
FROM (SELECT *
FROM #Dates
WHERE ShiftEnd >= @DateRangeStart AND ShiftEnd < @DateRangeEnd ) d
WHERE DATEPART(hh, ShiftEnd) = 20
-- (354 rows)
-- WITHOUT INDEX CPU time = 170 ms, elapsed time = 170 ms.
-- WITH INDEX CPU time = 0 ms, elapsed time = 2 ms. logical reads 26
---------------------------------------------------------------------
SET STATISTICS TIME OFF
SET STATISTICS io OFF
[/font]
In the absence of an index, Adi's query will always be fastest because dates in the table are checked as-is against the reference dates - the other methods have to check the return value of the function against the reference. DATEPART or CONVERT is applied to every single date in the table.
When the index is present, there's no contest. Adi's query is back at base with a cold beer while the others are figuring out which way to go through the dust.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 2, 2009 at 4:42 am
Excellent way to prove the best performing query. After going through the explanation, one can easily make up his mind as how good queries need to be written.
Thanks Chris for this 🙂
January 2, 2009 at 6:47 am
Thank you everyone, it was very helpful.
January 2, 2009 at 7:19 am
Chris Morris (1/2/2009)
...In the absence of an index, Adi's query will always be fastest because dates in the table are checked as-is against the reference dates - the other methods have to check the return value of the function against the reference. DATEPART or CONVERT is applied to every single date in the table...
Great Analysis Chris!
Just a couple of points that are perhaps for my benefit than anything else ;
i) with Adi's solution, will the DATEPART not be evaluated in any case ? You cannot assume SQL will short circuit...
ii) Will it really be checked "as-is" ? Is there not some conversion still required as the parameters are strings ?
iii) I presume the procedure and data caches were cleared in your testing ?
iv) Would it not be marginally quicker if Adi's query was refined as ;
select * from tablename
where dateColumn >= '20070101 20:00:00' and dateColumn < '20070301 21:00:00' (as Chirag's query)
thereby no functions are necessary ?
Please note this is not a criticism, I think your post is great, keep up the good work! My comments are merely to increase our understanding.
Regards
Preet
January 2, 2009 at 7:45 am
Hi Preet
Discussion is always good. Taking your points one by one:
i) with Adi's solution, will the DATEPART not be evaluated in any case ? You cannot assume SQL will short circuit...
in this case it's only evaluated on the relevant date range. You can't assume SQL will short circuit, you must test. My version of the query enforces the filters in two separate steps, date range first followed by time filter - I use this quite a lot, it ain't perfect but it usually works and as you can see it usually has little cost.
ii) Will it really be checked "as-is" ? Is there not some conversion still required as the parameters are strings ?
Now, why would you want to do that when it's so easy to convert the strings to datetimes?
iii) I presume the procedure and data caches were cleared in your testing ?
I ran the bunch of queries about 8 or 10 times with & without index, with the sample data generation part commented out. There's some variation in the timings which you will see if you try it for yourself, but those variations are much smaller than the differences between the methods.
iv) Would it not be marginally quicker if Adi's query was refined as ;
select * from tablename
where dateColumn >= '20070101 20:00:00' and dateColumn < '20070301 21:00:00' (as Chirag's query)
Nah, this doesn't work - it returns all datetimes in the date range, OP only wants datetimes in the date range where the time component is between 8pm and 9pm. Quicker doesn't count if the results aren't what you want.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 2, 2009 at 5:28 pm
Chris Morris (1/2/2009)
It's always nice to have some proof...and here it is...
Very, [font="Arial Black"]very [/font]well done... there's absolutely no argument when the arguments offered are in the form of viable, repeatable, demonstrable, working code. I wish more people would follow that example.
--Jeff Moden
January 3, 2009 at 3:18 am
Thanks Jeff :blush:
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply