November 21, 2013 at 6:42 am
Hi All,
I need help with returning a week range.
I need to pass in week number and year from a stored proc and return the output below but I don't want to limit the years and week numbers to the dates in my table, is there a way I can achieve this without using the dates in my table as I've done below.
Exec spcTest @years = '2013,2012', @week = '47' --use split function to get the years
Select CONVERT(VARCHAR, Convert(Varchar, (DATEADD(dd, -((DATEPART(dw, dteDate)+5)%7)-1, dteDate)), 111)) + ' - '+
CONVERT(VARCHAR, Convert(Varchar, (DATEADD(dd, -((DATEPART(dw, dteDate)+5)%7)+5, dteDate)), 111)) as WeekRange,
YEAR(dteDate) as [Year]
From test
WeekRangeYear
2012/11/18 - 2012/11/242012
2013/11/17 - 2013/11/232013
Please help..
Teee
November 21, 2013 at 7:27 am
I'm sure someone here has a cleaner solution but this should work:
DECLARE @WeekNum INT = 47
DECLARE @FirstYear INT = 2012
Declare @SecondYear int = 2013
SELECT convert(varchar(10),DATEADD(WEEK, @WeekNum - 1,DATEADD(dd, 1 - DATEPART(dw, '1/1/' + CONVERT(VARCHAR(4),@FirstYear)), '1/1/' + CONVERT(VARCHAR(4),@FirstYear))),111) + ' - ' +
convert(varchar(10),DATEADD(WEEK, @WeekNum,DATEADD(dd, 1 - DATEPART(dw, '1/1/' + CONVERT(VARCHAR(4),@FirstYear))-1, '1/1/' + CONVERT(VARCHAR(4),@FirstYear))),111)
SELECT convert(varchar(10),DATEADD(WEEK, @WeekNum - 1,DATEADD(dd, 1 - DATEPART(dw, '1/1/' + CONVERT(VARCHAR(4),@SecondYear)), '1/1/' + CONVERT(VARCHAR(4),@SecondYear))),111) + ' - ' +
convert(varchar(10),DATEADD(WEEK, @WeekNum,DATEADD(dd, 1 - DATEPART(dw, '1/1/' + CONVERT(VARCHAR(4),@SecondYear))-1, '1/1/' + CONVERT(VARCHAR(4),@SecondYear))),111)
November 21, 2013 at 7:37 am
Thank you 🙂 I'll try your solution..
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply