If you're using SQL Server 2000, it's more difficult. You have to use sysdatabases instead of sys.databases. Read the documentation about that and you'll see that the status...

No, you're not. FinMonth is an integer. Here's the proof from your original post:

[FinMonth] [int]

And here's what you're trying to do with it:

WHEN finmonth BETWEEN '2013-02-25' AND '2013-03-26'...

This is how you define the FinMonth column in #tempterminations, and therefore it's always going to be an integer between 1 and 12:

Datepart(mm, terminatedate) AS FinMonth

All you need to...

Please can we see your whole INSERT statement? I don't understand why, if you're inserting into FinMonth, you're testing for the value that's already there. Also, FinMonth is...

Something like this?

-- This uses a temp table, so if there's any chance

-- your session will close before you're finished,

-- use a permanent table instead.

-- You'll need to make it...

Do you only get this error after 6:30 pm? Are you a member of sysadmin on the server? If you are, there should be no reason you can't...

I've seen a lot of religious discussions about naming conventions. My advice would be to search this site and others and weigh up the pros and cons of each...

Hugo Kornelis (9/3/2013)

---

Thanks, John.

I had a look at the plans. Apparently the optimization steps I described were recently introduced. Your plans are in fact identical (I'll get to the extra...

Hugo

I'm on SQL Server 2008 R2 SP2. I ran both queries as a single batch, so there's a single execution plan, making it easy to compare them at a...

How about this?

SELECTCAST(LEFT(CounterDateTime, 23) AS SmalldateTime)

FROMCounterData

John

Awesome! Thanks for the in-depth analysis, Hugo, and for being more generous to me than I think I deserved. The first time I saw the queries, I assumed...

That suggests that the problem lies in what comes after the decimal point. What does this return?

SELECT LEN(CounterDateTime), COUNT(*)

FROM CounterData

GROUP BY LEN(CounterDateTime)

John

What does this return?

SELECTCounterDateTime

FROMCounterData

WHEREISDATE(CounterData) = 0

John

This is one of the problems with storing dates with the wrong data type. Not a criticism of you, since this probably isn't your design.

You only need one of...

There's very little difference. On SQL Server 2008 R2 SP2, there was an extra...