funooni (7/19/2010)
Going further from Eralper's idea;
To be fair, I think Lutz covered it first 🙂
You can use select DATEPART(WEEK,DAY(getdate())) to get the week of the month. GETDATE() can be replaced by any datetime variable or even a string like '2010-12-07' ; i am using 'yyyy-dd-mm' format.
This does not work. The DAY built-in function returns just the day date part of the supplied date, so for 19th July 2010, it would return 19.
Supplying the value 19 to the DATEPART function results in 19 being implicitly converted to a date (19 days after the base date '1900-01-01') to give '1900-01-20'. Knowing the week number of 20th January 1900 is unlikely to be the desired result 😉
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi