Tuesday, December 14, 2010

Start of Week

Working with a developer last week to find for any given date the Monday of the week the date falls within. Several scripts online but none seemed to work reliably so the script below was written. Two parameters are used, @currdate, the date within the week, and @startdate, the first day of the year for @currdate.

DECLARE @currdate DATETIME
DECLARE @startdate DATETIME
SET @currdate = GETDATE()
SET @startdate = '01/01/' + CONVERT(CHAR(4),@currdate,112)
select DATEADD(wk, DATEDIFF(wk, 7, dateadd(dd,(datepart(dy,@currdate))-1,@startdate)), 7)
select DATENAME(dw,DATEADD(wk, DATEDIFF(wk, 7, dateadd(dd,(datepart(dy,@currdate))-1,@startdate)), 7))