Get dates(Firstday or Lastday) from week number in T-SQL
How to convert a week number to valid Datetime
At first create a SQL Server user defined Function( like below)
CREATE function fn_week2date ( @YearNum as int , @WeekNum as int , @FirstOrLastDay bit )
RETURNS DATETIME
begin
declare @res as datetime
if @FirstOrLastDay = 0
SELECT @res = DATEADD(wk, DATEDIFF(wk, 6, convert( datetime , '1.1.' + convert( varchar , @YearNum) , 104 ) ) + (@WeekNum-1), 6)
else
SELECT @res = DATEADD(wk, DATEDIFF(wk, 5, convert( datetime , '1.1.' + convert( varchar , @YearNum) , 104 ) ) + (@WeekNum-1), 5)
return @res
end
Than you can execute the function according to your needs
At first create a SQL Server user defined Function( like below)
CREATE function fn_week2date ( @YearNum as int , @WeekNum as int , @FirstOrLastDay bit )
RETURNS DATETIME
begin
declare @res as datetime
if @FirstOrLastDay = 0
SELECT @res = DATEADD(wk, DATEDIFF(wk, 6, convert( datetime , '1.1.' + convert( varchar , @YearNum) , 104 ) ) + (@WeekNum-1), 6)
else
SELECT @res = DATEADD(wk, DATEDIFF(wk, 5, convert( datetime , '1.1.' + convert( varchar , @YearNum) , 104 ) ) + (@WeekNum-1), 5)
return @res
end
Than you can execute the function according to your needs
- Get the First day of given Week number and Year
declare @d as datetime
select @d = dbo.fn_week2date( 2010 , 20 , 0 )
print @d - Get the last day of given Week number and Year
declare @d as datetime
select @d = dbo.fn_week2date( 2010 , 20 , 0 )
print @d
Comments
16.08.2011
Donie
Thanks so much for that code! It saved me a lot of time. Thanks!
Leave a trace
Post being viewed right now
