The beginning and end of a week (T-SQL)


 
--SELECT @@DATEFIRST --  7 (default, U.S. English) First day of the week is Sunday

--Set to meet your setting
SET DATEFIRST 1 --First day of the week is Monday

declare @startdate date='2014-07-01'
declare @enddate date='2015-07-31'

--Use some functions avalaible to SQL Server 2012

;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n<101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)

,dates as (
Select  dateadd(day,n-1,@startdate) dt from Nums
WHERE dateadd(day,n-1,@startdate)<=@enddate
)

,yourdtset as (
SELECT dt, FIRST_VALUE(dt)Over(Partition by DATEPART(year,dt), DATEPART(week,dt)  Order by dt)  WeekFrom
,FIRST_VALUE(dt)Over(Partition by DATEPART(year,dt), DATEPART(week,dt) Order by dt DESC)  WeekEnd 
,ROW_NUMBER () OVER (Partition by DATEPART(year,dt), DATEPART(week,dt)  Order by dt ) rn
FROM dates
)

  Select 'Week No' + Cast(ROW_NUMBER () OVER (ORDER BY dt) as varchar(5)) +':' as [WeeekNum]
  ,Format(WeekFrom,'d-MMMM-yyyy')  WeekFrom  
  ,Format(WeekEnd,'d-MMMM-yyyy')  WeekEnd
   from yourdtset
   WHERE rn=1


--set to my local
SET DATEFIRST 7 

 

https://social.msdn.microsoft.com/Forums/en-US/1fd8ba69-b638-447a-a457-c630ec136be2/how-to-generate-week-ranges-like-this-picture-in-sql?forum=transactsql

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s