Display number of days/weeks in the year using SQL Query

Each time if i develop any report i need to write SQL Query for list out number of days or week in the year. So here i am giving you the simplified way to get the number of days name,date and week number of each date. One of my requirement is to display each week the sales report for the product. So the below query will help you to list out number of weeks in the year with week name.


SQL Query:

DECLARE @Year AS INT,
@FirstDateOfYear DATETIME,
@LastDateOfYear DATETIME
-- You can change @year to any year you desire
SELECT @year = 2016
SELECT @FirstDateOfYear = DATEADD(yyyy, @Year - 1900, 0)
SELECT @LastDateOfYear = DATEADD(yyyy, @Year - 1900 + 1, 0)

;WITH cte AS (
SELECT 1 AS DayID,
@FirstDateOfYear AS DateList ,
DATENAME(dw, @FirstDateOfYear) AS DayName

UNION ALL

SELECT cte.DayID + 1 AS DayID,
DATEADD(d, 1 ,cte.DateList),
DATENAME(dw, DATEADD(d, 1 ,cte.DateList)) AS DayName
FROM cte
WHERE DATEADD(d,1,cte.DateList) < @LastDateOfYear

)
SELECT *  ,'Week '+Convert(nvarchar,DATEPART(wk,DateList)) as WeekName
FROM CTE

OPTION (MaxRecursion 370)


The Result :

DayID	DateList	DayName	WeekName
1	1-Jan-16	Friday	Week 1
2	2-Jan-16	Saturday	Week 1
3	3-Jan-16	Sunday	Week 2
4	4-Jan-16	Monday	Week 2
5	5-Jan-16	Tuesday	Week 2
6	6-Jan-16	Wednesday	Week 2
7	7-Jan-16	Thursday	Week 2
""	""	""	""
""	""	""	""
366	31-Dec-16	Saturday	Week 53

The above SQL query we will get the day count start from 1 to end with 365 or 366 and display the date and the Date name. Finlay added week number in the list so when you want only week of the day or number of weeks in the year you can use only the WeekName column.You can use DISTINCT option to display only week number on the display result. We can make this query to our convenience way to get proper result set.