how to get startdate and enddate as well week no..

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

how to get startdate and enddate as well week no..

Author

Topic

asifbhura
Posting Yak Master

165 Posts

Posted - 2014-05-21 : 00:00:24

Hello,

I want to have list of week no. and its startdate and enddate of given
month and year
suppose

I have month no: 5(May) and year(2014)

i want output like

Start Dates End Dates week nos.

regards,
ASIF

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2014-05-21 : 01:11:32

DECLARE @Month CHAR(10) = '10',@Year CHAR(10) = '2014'

DECLARE @SDate DATE,@EDate DATE
SET @SDate = CONVERT(DATE,@Month + '/01/' + @Year)
SET @EDate = DATEADD(MONTH,1,@SDate)
SET @EDate = DATEADD(D,-1,@EDate)

; WITH CTE(WeekId,Date) AS
(
SELECT DATEPART(Wk,@SDate),@SDate

UNION ALL

SELECT DATEPART(Wk,DATEADD(D,1,Date)),DATEADD(D,1,Date)
FROM CTE
WHERE Date < @EDate
)
SELECT WeekId
, MIN(Date) AS 'StartDate'
, MAX(Date) AS 'EndDate'
FROM CTE
GROUP BY WeekId

Veera

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources