IMO , a calendar table is essential.

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

If your datefirst is sunday then

you can simply use "datediff(ww" , since that counts the number of 'boundaries' not calendar weeks

Can you post the execution plan for the query Without the index hint ?

This is the wrong place to be validating data cleanliness and you should be using the correct datatypes. This has many many advantages.

You could test the data using the...

A calendar table is essential imho

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

How do you mean 'fails to do the conversion' ? Do you have data that does not match a date format in your 'date' fields? You should really...

Please see

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

and

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

Jeff Moden (7/22/2010)

The moral of the story is DO NOT RELY ON ESTIMATED OR ACTUAL EXECUTION PLAN COSTS OR PERCENT-OF-BATCH TO SELECT THE MOST PERFORMANT QUERY BECAUSE THE EXECUTION PLAN...

No difference, !< is translated to be >= by the engine. Try both and have a look at the predicates in the execution plan

Clustered index scans probably due to the function use.

Did you try the code Chris suggested ?

Additionally can you clarify you index strategy ?

In the spreadsheet you put this...

So...

Though im not going to argue with your analysis, the premise of the article

"Best practices call for tempdb to have one datafile per processor (core)"

is based on a myth

http://sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-%281230%29-tempdb-should-always-have-one-data-file-per-processor-core.aspx

"So,...

Just to add...

You have no idea what these guys have left on your machine. Your entire network has been compromised at its base level. If i were you...

This

http://www.sqlservercentral.com/Forums/Topic953765-146-1.aspx

could be an interesting one to watch

Are they from an internal network or from the internet.

Please tell me that you havent been running with a blank sa password open to the internet.

If internal , get your...

Can you show us what you have got in terms of code already ?

And for once , i was able to say BEST 🙂 *cough* other search engines are available *cough*