Get start and end date from week number

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 2008 Forums

Transact-SQL (2008)

Get start and end date from week number

Author

Topic

chiragvm
Yak Posting Veteran

65 Posts

Posted - 2013-05-20 : 04:09:40

Hi to all
i want to get start date and end date of the week from week number

suppose i am pass 20 in a function
function will return me a week start date and end date

-------------
Chirag
India
Sr. Sw.Engineer

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-20 : 04:16:46

See illustration below


DECLARE @WeekNo int= 4
DECLARE @Year int=2013

SELECT DATEADD(wk,@WeekNo-1,DATEADD(yy,@Year-1900,0)) AS WeekStart,
DATEADD(wk,@WeekNo,DATEADD(yy,@Year-1900,0))-1 AS WeekEnd

you can turn it into a UDF

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

chiragvm
Yak Posting Veteran

65 Posts

Posted - 2013-05-20 : 04:27:22

thanks visakh16 for quick answer
but it will give me tuesday to monday i want sunday to saturday

i am pass 20 and result is
WeekStart = 2013-05-14 00:00:00.000
WeekEnd = 2013-05-20 00:00:00.000

Result Should be
WeekStart = 2013-05-12 00:00:00.000
WeekEnd = 2013-05-18 23:59:59.000

-------------
Chirag
India
Sr. Sw.Engineer

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-20 : 05:07:15

--Try this


DECLARE @WeekNum INT
      , @YearNum char(4);

SELECT @WeekNum = 20 
     , @YearNum = 2013 
-- once you have the @WeekNum and @YearNum set, the following calculates the date range.
SELECT DATEADD(wk, DATEDIFF(wk, 6, '1/1/' + @YearNum) + (@WeekNum-1), 6) AS StartOfWeek;
SELECT DATEADD(wk, DATEDIFF(wk, 5, '1/1/' + @YearNum) + (@WeekNum-1), 5) AS EndOfWeek;

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources