[dba-SQLServer] Current Week in SQL

On 19 Jun 2006 at 13:03, Darren DICK wrote:

> Hi all
> 
> Is there an easy way to determine the date of the Monday in the
> current week - Also the same for the Sunday of the current week?
> 
> E.g. - Assume today is Wednesday the 3rd April
> 
> I need help to determine that Monday is the 1st April 2006 and Sunday
> would be the 7th April
> 
> Just need to work our the Monday date and Sunday date of any week we
> are in
> 

Does your week start on Monday?  There are different conventions for this.

Assuming this is so, the following function will get Monday. Slight modifications will get you 
the Sunday(last day of the week = 7) and by plugging other dates in place of GetDate(), you 
can do it for any week.

declare @date datetime
set DATEFIRST  1  /* Set Monday as first day of the week */
set @date = GetDate() 
select convert(nvarchar(12), at date + 1 - datepart(dw, at date),6)