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

No comments:

Post a Comment