Handy SQL Functions: Reverse Week Number

I thought it was about time I posted some of the random little functions I've created in SQL that I use now and then.

So here's the first. It's very easy in SQL to get a week number (1..53) from a date - just use "datepart". However doing the reverse isn't. Say, for instance you wanted to know what the date was at the start of week 27 in 2012. How would you go about working that out? Here's my solution.

Just supply the function with the week number and year and out pops the date.

CREATE FUNCTION dbo.fnGetDateFromWeekNo
(@weekNo int , @yearNo  int)
RETURNS smalldatetime
AS
BEGIN 

DECLARE @tmpDate smalldatetime

IF @weekNo<1 set @weekNo=1

set @tmpdate= cast(cast (@yearNo as varchar) + '-01-01' as smalldatetime)
-- jump forward x-1 weeks to save counting through the whole year 
set @tmpdate=dateadd(wk,@weekno-1,@tmpdate)

-- make sure weekno is not out of range
if @WeekNo <= datepart(wk,cast(cast (@yearNo as varchar) + '-12-31' as smalldatetime))
BEGIN
 WHILE (datepart(wk,@tmpdate)<@WeekNo)
 BEGIN
  set @tmpdate=dateadd(dd,1,@tmpdate)
 END
END
ELSE
BEGIN
 -- invalid weeknumber given
 set @tmpdate=null
END


RETURN @tmpDate

END