I've tried both datetime and smalldatetime and have the same issue with both

Got it to work doing this as recommended by Joel

SET @pdteStartDate = CONVERT(char(10), @pdteStartDate, 103)

I was doing this for example

DATEADD(m,5,@pdteStartDate)

and 5 months was being added to the day component rather than the month component of @pdteStartDate, however when I passed in the same parameter...

Thanks for your response. However, should I really be having to convert a date back to the format I want. I just can't understand why this database is running like...

The scenario is purely hypothetical. I have a document library I am building with thousands of records and the index would be very useful to be presented on a webpage

Just tried it. Oh my god, this works amazingly well. I've brielfy heard of CTEs before, but never had a good use for them until now.

I'm impressed!!!

Just tried it. Oh my god, this works amazingly well. I've brielfy heard of CTEs before, but never had a good use for them until now.

I'm impressed!!!

Just tried it. Oh my god, this works amazingly well. I've brielfy heard of CTEs before, but never had a good use for them until now.

I'm impressed!!!

Thanks very much for your reply, I'll give that a go. My boss in work always tells me how good Books Online is and I never bother to look.

That works brilliantly. I'm starting a new job on 2nd January and have been trying to tone up my SQL skills before then.

I'm surprised that I had so many responses!

Haven't...