Showing posts with label butuntil. Show all posts
Showing posts with label butuntil. Show all posts

Friday, March 9, 2012

Function Return First Date of Week of Year

Hi
I've been using this function to return the date og the 'w of year' - but
until 2004-53 - > it calculates wrong.
W 53-2004 -> 2004/12/27 OK
W 01-2005 -> 2005/01/01 Wrong
W 02-2005 -> 2005/01/02 Wrong
W 03-2005 -> 2005/01/10 OK
btw: I have the Set DateFirst 1 ( Monday )
Can anyone Help?
And here the function
create FUNCTION WEEK_TO_DATE(@.w int, @.year int)
RETURNS datetime
AS
BEGIN
declare @.date datetime
set @.date = convert(datetime, '1/1/' + cast(@.year as char(4)) )
while datepart(wk, @.date) <> @.w
set @.date = dateadd(dd, 1, @.date)
RETURN @.date
END
Kind Regards
J. E. JensenTake a look at the ISOWEEK function under the CREATE FUNCTION topic in
Books Online.
David Portas
SQL Server MVP
--