MS SQL Tips & Tricks

How to find first and last day of the week

To finst first and last day of the week You can use script bellow. First code treats Saturday as a first of the week 

declare @cDateTime datetime;

set @cDateTime = GetDate()

select dateadd( week, 
		datediff( week, 0, @cDateTime ), 
		0 ) as BeginOfTheWeek
		 
select dateadd(d,
		-1,
		dateadd( week, 
		datediff( week, 0, @cDateTime )+1, 
		0 )
		) as EndOfTheWeek

The second sample assumes that week starts on Monday and ends on Saturday

declare @cDateTime datetime;
set @cDateTime = '2009-05-17'

select dateadd( week, 
		datediff( week, 0, dateadd(d,-1,@cDateTime) ), 
		0 ) as BeginOfTheWeek;
		 
select dateadd(d,
		-1,
		dateadd( week, 
		datediff( week, 0, dateadd(d,-1,@cDateTime) )+1, 
		0 )
		) as EndOfTheWeek
Download SQL script

See also


Learn more tricks

Add Comment

Comments