-- The following sets the first day of the week as Monday
SET DATEFIRST 1
-- Given a date
DECLARE @tDate datetime
SET @tDate = '10/1/2006'
-- Week number in a month for a specific date
SELECT (datepart(ww,@tDate)) + 1- datepart(ww,dateadd(dd,-datepart(dd,@tDate),@tDate)) nWeek
-- Week Number of the Year
SELECT DATEPART(WK, @Month)
-- First Day of the Month
SELECT DATEADD(mm, DATEDIFF(mm,0,@Month), 0)
Some links where you can find more sql date functions are as follows:
http://www.databasejournal.com/features/mssql/article.php/3076421
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=575&lngWId=5
NovickSql.com has many sql functions that can be useful in the daily life of a sql server developer
