how to find week start date and end date from a given week number

Question

Post reply

how to find week start date and end date from a given week number

swarooppavi

Old Hand

Points: 305
More actions
October 24, 2008 at 5:12 am

#77122

how to find week start date and end date from a given week number

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Garadin One Orange Chip Points: 29613 More actions · Answer 1

Since many companies have their own dates for when weeks start and end (ie. payweeks, Thu - Wed, or Mon - Sun, etc), I normally generate a table that encompasses all the weeks/weeknumbers for the next say, 20 years and just use that to look it up. Over time, there are often many queries that will end up making use of such a table.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

swarooppavi Old Hand Points: 305 More actions · Answer 2

swarooppavi

Old Hand

Points: 305

October 24, 2008 at 6:52 am

#889178

If I pass week number, i need to get that weeks Monday date and Sunday date

Garadin One Orange Chip Points: 29613 More actions · Answer 3

Garadin

One Orange Chip

Points: 29613

October 24, 2008 at 6:55 am

#889181

And what is week number? It could be defined in several different ways.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 4

Here's another post handling this issue...

http://qa.sqlservercentral.com/Forums/FindPost570917.aspx

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

john.steinbeck Ten Centuries Points: 1015 More actions · Answer 5

I found the top portion and applied that to Jeff Modens Tally Table concept

See if it's what you want...



DECLARE @BASEYEAR		CHAR(4)

DECLARE @WEEKNO			INT

DECLARE @TARGETWEEKDAY	INT

DECLARE @BASEDATE		DATETIME   

SET @baseYear = '2008'

SET @weekNo = 43

SET @targetWeekDay = 2

SET @baseDate = CONVERT(DATETIME, @baseYear + '0101')

DECLARE @BEGINWEEK		DATETIME

SET datefirst 7

SET 	@BEGINWEEK=

		@baseDate +

		(14 + @TARGETWEEKDAY - DATEPART(dw, @BASEDATE) - @@DATEFIRST)%7

        + (@WEEKNO - 1)*7

--		AS Nth_Target_Date

DECLARE @DateStart DATETIME

DECLARE @DateEnd   DATETIME

SELECT  @DateStart = @BEGINWEEK,

		@DateEnd   = DATEADD(yy,20,@DateStart)

--===== Display the shift number and date/times

SELECT

DATEADD(D,(T.N-1),@DateStart) AS [WEEKSTART - MON],

DATEADD(D,6*(T.N  ),@DateStart) AS [WEEKEND - SUN]

FROM DBO.TALLY T

WHERE DATEADD(hh,6*(T.N-1),@DateStart) <= @DateEnd

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 6

swarooppavi (10/24/2008)

If I pass week number, i need to get that weeks Monday date and Sunday date

Not sure if this helps, but here's some code that accepts a date and returns the Monday and Sunday dates of that week.



SELECT DATEADD(wk, DATEDIFF(wk, 7, GetUtcDate()), 7) AS Monday

SELECT DATEADD(wk, DATEDIFF(wk, 5, GetUtcDate()), 6) AS Sunday