Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Searching Dates
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

Russell M
Starting Member

2 Posts

Posted - 06/26/2005 :  21:15:39  Show Profile
SQL Server 2000
Searching a DateTime field.

To search by "DAY" and "Hour" only...
Month, year, minute, second makes no difference.

There are 2 ways.
Using Query Analizer and looking at execution plan
This is what I get.

Using QA Estimated QueryPlan.
WHERE Day(jDate) = 23 AND DATEPART( hh, jDate ) = 18
In QA I get
Select = 22%
Index Scan = 78%

'Using 4 underscores ____ in place of year
ie:'%23_2004__6%' Without year '%23_______6%'

Where jDate LIKE '%23_______6%'
In QA I get
Select = 18%
index Scan = 82%

This is on 200,000 rows in table
and queries return about 2000

Seems like Query2 returns the rows to QA faster...

How does one time it?

in QA hovering the tableName it shows
Index Scan
object = Select ... from .... where WHERE:( Day(jDate) = 23 AND DATEPART( hh, jDate ) = 18)

Now for Query2 the where changes to a convert...

index Scan
object = Select ... from .... where:(LIKE(Convert(jDate),'%23_______6%',NULL))

Can anyone tell or know for sure what would be faster if searching 1,000,000's of rows in table?

Or any way to accurately time it?

Thanks in advance.
Russell M

White Water Yakist

3467 Posts

Posted - 06/26/2005 :  22:27:11  Show Profile
Originally posted by Russell M

How does one time it?

-- exec your query here


My impression is that converting a DATETIME (jDate) to character format so a LIKE wildcard comparison could be done would be a major loser.

Without further ado, I predict Query 1 will be the winner.

If you're really concerned about performance, add a couple of TinyINT columns to store the Day and Hour of the jDate DATETIME column and index on these columns accordingly... (You could write a trigger to set these columns whenever jDate is modified.)

Then, maybe you could do with a single column that holds day/hour together...
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 06/26/2005 :  22:48:02  Show Profile
Nothing you do is going to be very efficient, because the best you can hope for is an index scan on jDate, if there is an index on that column.

This should be faster than the LIKE, because the datediff and dateadd functions are much faster than conversions to strings. It works by finding the hour of the month for jDate and comparing it to the hour of the month for the month/day combination you are looking for:

	datediff(hh,0,dateadd(mm,0-datediff(mm,0,jDate),jDate)) = 
	datediff(hh,0,convert(datetime,'1900-01-23 18:00:00'))

You could also compute these numbers this way:

	((Day(jdate)-1)*24)+datepart(hh,jdate) =

As with anyting, you should test to see what actually produces the best results.


Edited by - Michael Valentine Jones on 06/27/2005 07:30:34
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000