TSQL: Svenskt År+veckonummer från DateTime

Att få svenskt veckonummer i SQLServer mha en user function. Man måste dessvärre sätta "SET FIRSTDATE 1" (alt SET DATEFORMAT ymd) innan man använder sig av den.

Ful lösning men som fungerar.

GetYearWeeks() returnerar antal veckor på ett år.

GetYearWeekString() returnerar en sträng bestånde av "<år>;<vecka>". Då 1 jan 2005 tillhör vecka 53 år 2004.

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

ALTER   FUNCTION GetYearWeeks(@year int)
RETURNS int
AS
BEGIN

--	set datefirst 1
--	set dateformat ymd

	declare @numWeeks int
	set 	@numWeeks =	datepart(ww, convert(varchar, @year) + '-12-31')
	declare @firstJanDate1 datetime
	declare @firstJanDate2 datetime
	set	@firstJanDate1 =	convert(varchar, @year) + '-01-01'
	set	@firstJanDate2 =	convert(varchar, @year+1) + '-01-01'
	declare @firstJanWeekday1 int
	declare @firstJanWeekday2 int
	set	@firstJanWeekday1 =	datepart(dw, @firstJanDate1)
	set	@firstJanWeekday2 =	datepart(dw, @firstJanDate2)

--print @firstJanWeekday1
--print @firstJanWeekday2

	if @firstJanWeekday1 >= 5
		set @numWeeks =	@numWeeks - 1
	if @firstJanWeekday2 <= 4 and @firstJanWeekday2 > 1
		set @numWeeks =	@numWeeks - 1

  RETURN(@numWeeks)
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

ALTER   FUNCTION GetYearWeekString(@date datetime)
RETURNS varchar(50)
AS
BEGIN

--	set datefirst 1
--	set dateformat ymd
		
	declare @return varchar(50)
	
	declare @weekday int
	set	@weekday =	datepart(dw, @date)
	declare @week int
	set 	@week =		datepart(wk, @date)
	declare @year int
	set 	@year =		year(@date)
	declare @numWeeks int
	set 	@numWeeks =	dbo.GetYearWeeks(@year)
	declare @firstJanDate1 datetime
	declare @firstJanDate2 datetime
	set	@firstJanDate1 =	convert(varchar, @year) + '-01-01'
	set	@firstJanDate2 =	convert(varchar, @year+1) + '-01-01'
	declare @firstJanWeekday1 int
	declare @firstJanWeekday2 int
	set	@firstJanWeekday1 =	datepart(dw, @firstJanDate1)
	set	@firstJanWeekday2 =	datepart(dw, @firstJanDate2)
	declare @diffFromNewYear int
	set 	@diffFromNewYear =	datediff(d, @date, @firstJanDate2)
	
	
	declare @weekDiff int
	set	@weekDiff =	0
	-- if 1 jan is friday-sunday, subtract one week.
	if @firstJanWeekday1 >= 5
	begin
		set @weekDiff =	@weekDiff - 1
		set @week =	@week + @weekDiff
	end
	
	if
		(
			@diffFromNewYear <= (@firstJanWeekday2-1)	-- number of weekdays to previous year
			and 
			@firstJanWeekday2 <= 4
		)
		begin
		set @return  =	convert(varchar, @year+1) + ';1'
		end
	else
	begin
		if @week = 0
			-- get max week for previous year.
			set @return  =	convert(varchar, @year-1) + ';' + convert(varchar, dbo.GetYearWeeks(@year-1))
		else
		begin
			if @week > @numWeeks
				set @week = @numWeeks
			set @return  =	convert(varchar, @year) + ';' + convert(varchar, @week)
		end
	
	end

  RETURN(@return)
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO