For europe weeks use:
CREATE function dbo.f_GetKW (@Datum datetime) Returns int
AS
BEGIN
declare @Tag int
declare @Monat int
declare @Jahr int
declare @a int
declare @b int
declare @c int
declare @KW int
declare @JD int
set @Tag = datepart(d, @Datum)
set @Monat = datepart(m, @Datum)
set @Jahr = datepart(yyyy, @Datum)
-- Julianische Tagesnummer berechnen
-- erster Tag = 01.01.-4713, letzter Tag = 22.01.3268
set @JD = (1461 * (@Jahr + 4800 + (@Monat - 14) / 12)) / 4 +
(367 * (@Monat - 2 - 12 * ((@Monat - 14) / 12))) / 12 -
(3 * ((@Jahr + 4900 + (@Monat - 14) / 12) / 100)) / 4 +
@Tag - 32075
-- daraus KW berechnen
set @a = (@JD + 31741 - (@JD % 7)) % 146097 % 36524 % 1461
set @b = @a / 1460
set @c = ((@a - @b) % 365) + @b
set @KW = @c / 7 + 1
Return @KW
END
--
with greetings from Spöck
[quoted text, click to view] "Tibor Karaszi" wrote:
> DAEFIRST is inherited from the language you have specified for your login. Try below and you will
> see that different languages has different default DATEFIRST:
>
> EXEC sp_helplanguage
>
> But in any case, if you expect to even find Jan 1 to be week number 52 (as we do in Europe, don't
> use DATEPART.
> --
> Tibor Karaszi, SQL Server MVP
>
http://www.karaszi.com/sqlserver/default.asp >
http://www.solidqualitylearning.com/ > Blog:
http://solidqualitylearning.com/blogs/tibor/ >
>
> "Mauro Miotello" <sviluppo@ashnet.it> wrote in message news:dsnngc$vr8$1@nnrp.ngi.it...
> > Ok but i don't understand why same SO, same SQLExpresse, same code, same data & different result
> > !!
> >
> > thanks
> >
> > mauro
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> ha scritto nel messaggio
> > news:eYt$%239%23LGHA.2604@TK2MSFTNGP09.phx.gbl...
> >> In addition, SQL Server never calculated week number the same way as "the rest of the word" and
> >> also the way that the ISO standard specifies. Use the ISOWeek function, found in Books Online or
> >> a calendar table instead of the DATEPART function.
> >
> >
>
