Get Start of the Week based on week

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

General SQL Server Forums

Script Library

Get Start of the Week based on week

Author

Topic

xianve
Starting Member

3 Posts

Posted - 2009-12-11 : 15:06:44

Hi Everyone,

Just wanted to check with everyone.
Need a function to calculate the first day of the week (sun) for a given week number. Here's my first stab at it.


DECLARE @year int = 2010
DECLARE @cw int = 30

DECLARE @yearOffset int = 1900
DECLARE @offset int = -((@year-@yearOffset)%7)

DECLARE @dayOfYear smalldatetime = CONVERT(smalldatetime,(7*(@cw))) -- year is 1900 by default
DECLARE @currentDate smalldatetime = DATEADD(yy,(@year-@yearOffset),@dayOfYear) -- add offset to 1900
DECLARE @startOfWeek smalldatetime = DATEADD(dd,@offset,@currentDate) -- calc sunday start

SELECT @startOfWeek

-- one liner
SELECT DATEADD(dd,-((@year-1900)%7),DATEADD(yy,(@year-1900),CONVERT(smalldatetime,(7*(@cw)))))

Let me know if there are any errors.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-11 : 15:16:57

It'll help to have a look at this link.

http://weblogs.sqlteam.com/peterl/archive/2009/06/17/How-to-get-the-Nth-weekday-of-a-month.aspx

Peter has already done some extensive research on this area I guess..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources