first day of week by Week Number

I was looking for a solution for this problem - but none of what I
found in Internet helped - so finally I made something myself ( based a
little bit on someone's idea). I hope it will be useful to someone - or
maybe u'll see some bugs in it? please report.
there 2 assumptions
1. before executing this function u SET DATEFIRST 1
2. u have already an IsoW function implemented ( found in Books
Online as example for CREATE FUNCTION)
create function fdaywk (@.year int, @.w int )
returns datetime
as
begin
declare @.date datetime
begin
set @.date = cast('01/01/' + cast(@.year as char(4))as datetime)
if datepart(dw,@.date)>4
set @.date = @.date+8-datepart(dw,@.date)
else
set @.date= @.date-datepart(dw,@.date)+1
set @.date = dateadd(wk, @.w-1, @.date)
end
return(@.date)
endHi,
your solution wont work on other servers than US or english one, using
US regional settings.
The code in cause is :
set @.date = cast('01/01/' + cast(@.year as char(4))as datetime)
You must use the short ISO date encode wich is SQL Server World Wide
portable solution :
YYYYMMDD HH:MM:SS.nnn
Try it :
SELECT CAST('20050225 11:23:17.852' AS DATETIME) AS DT
This datetime format is corresponding to CONVERT(... 121)
A +
tomek a crit :
> I was looking for a solution for this problem - but none of what I
> found in Internet helped - so finally I made something myself ( based a
> little bit on someone's idea). I hope it will be useful to someone - or
> maybe u'll see some bugs in it? please report.
> there 2 assumptions
> 1. before executing this function u SET DATEFIRST 1
> 2. u have already an IsoW function implemented ( found in Books
> Online as example for CREATE FUNCTION)
> create function fdaywk (@.year int, @.w int )
> returns datetime
> as
> begin
> declare @.date datetime
> begin
> set @.date = cast('01/01/' + cast(@.year as char(4))as datetime)
> if datepart(dw,@.date)>4
> set @.date = @.date+8-datepart(dw,@.date)
> else
> set @.date= @.date-datepart(dw,@.date)+1
> set @.date = dateadd(wk, @.w-1, @.date)
> end
> return(@.date)
> end
>
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************