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

Question

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

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

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: 135369 More actions · Answer 4

Here's another post handling this issue...

http://www.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 @BASEYEARCHAR(4)

DECLARE @WEEKNOINT

DECLARE @TARGETWEEKDAYINT

DECLARE @BASEDATEDATETIME

SET @baseYear = '2008'

SET @weekNo = 43

SET @targetWeekDay = 2

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

DECLARE @BEGINWEEKDATETIME

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