Search This Blog

Wednesday, April 4, 2018

Get all week based on month and year using SQL Server


Get all week based on month and year using SQL Server:
DECLARE @Year char(4), @iValue TINYINT, @Month tinyint
--//Populate your parameter values
 SET @Year = '2018';

select * into #temptest from (

SELECT DATEPART(wk,DATEADD(wk,t2.number,@Year)) as Weeknumb,( SELECT DATENAME(month, DATEADD(month,  month(DATEADD(wk,t2.number,@Year + '/01/01')), -1 ))) AS Month
FROM master..spt_values t2
WHERE t2.type = 'P'
AND t2.number <= 255
AND YEAR(DATEADD(wk,t2.number,@Year))=@Year) as T


select 'Week-' + convert(varchar(50), ROW_NUMBER() OVER( ORDER BY Weeknumb)) as Week, 
Weeknumb,Month  from #temptest where Month='April'


No comments:

Post a Comment

Note: Only a member of this blog may post a comment.