Try here

Are you running SSMS on the server itself or on another machine?

If your not running it, and the command prompt, on the same machine then you are obviously going to...

Michael,

I'd completely forgotten about that little gem. Well remembered.

Sure, setting MAXRECUSRION will work, but at what expense?

Grant even said in his post that it wasn't the greatest approach.

I would avoid recursive CTEs if there is a more efficient...

Jonathan,

Minor problem, your query doesn't get the 11th, 12th and 13th correct, you get 11st, 12nd and 13rd instead. Oops! 🙂

Have been pondering this and have come up with an alternative that uses a Tally table:

DECLARE @d1 datetime, @d2 datetime

SELECT

@d1...

Apologies I posted a slightly incorrect query, given your requirements. It should have:

COUNT(*)

in place of the:

DATENAME(dw,date)

Here's an alternative that doesn't use a recursive CTE to generate the dates.

DECLARE @d1 datetime, @d2 datetime

DECLARE @n int

SELECT

...

An this is slightly faster again, by avoiding the string comparison ( = 'Friday'):

SELECT

...

Bill Coale (8/27/2009)

---

I agree with Rimvydas' approach.

And for performance concerns on CTEs on this particular problem, this gives all the years from 1753 to 9999 that have 3 Friday the...

Another set based solution, this time using the good old Tally table[/url]. ((c) Jeff Moden et al. 19xx-2009)

SELECT TOP 1

DATEADD(dd,N-1,GETDATE())

...

Steve,

You say they won't do this due to a loss of revenue from selling fewer Windows licences.

Surely Micro$oft will just charge more for this 'SQL OS' to make up the...

You can use the DatePart function to extract components of a date.

See here

Glad we could be of help.

But please keep in mind Elliot's warnings about comparisons with dates when truncating the times.

Nigel

Elliot,

The original poster subtracted a whole second from midnight giving a time of 23:59:59.000

which if used in a where clause has the potential to miss rows with a time component...