SQL Server Holidays Netherlands

SQL Server Dutch Holidays function

CREATE function dbo.Is_feestdag(@checkdatum date)
  RETURNS nvarchar(20) as
BEGIN
    DECLARE @D2 INT; 
    DECLARE @D3 INT; 
    DECLARE @D4 INT; 
    DECLARE @D5 INT;
    DECLARE @D7 INT; 
    DECLARE @D10 INT; 
    DECLARE @D13 INT; 
    DECLARE @D14 INT; 
    DECLARE @D15 INT;
    DECLARE @datum DATE;
    DECLARE @omschr NVARCHAR(20);
    DECLARE @yyyy INT=DATEPART(year,@checkdatum);
    DECLARE @result nvarchar(20);

	--koningsdag in @yyyy
	IF @yyyy<1949
	BEGIN
		SET @datum=DATEFROMPARTS(@yyyy,8,31);
	END
	ELSE 
	BEGIN
		IF @yyyy<2014
		BEGIN
			SET @datum=DATEFROMPARTS(@yyyy,4,30);
		END
		ELSE
		BEGIN
			SET @datum=DATEFROMPARTS(@yyyy,4,27);
		END
	END;
	-- op zo dan een dag eerder
	IF DATEPART(DW,@datum)=1
		SET @datum=DATEADD(day,-1,@datum);

	-- za/zo
	IF DATEPART(DW ,@checkdatum) in (1,7)
	BEGIN 
		SET @result='weekend' 
	END
	ELSE
	BEGIN
		-- koningsdag / nieuwjaar / kerst
		IF @checkdatum=@datum 
		BEGIN 
			SET @result='koningsdag' 
		END
		IF @checkdatum=DATEFROMPARTS(@yyyy,1,1) 
		BEGIN 
			SET @result='nieuwjaar' 
		END
		IF  @checkdatum=DATEFROMPARTS(@yyyy,12,25) 
		BEGIN 
			SET @result='kerst' 
		END
		IF @checkdatum=DATEFROMPARTS(@yyyy,12,26)
		BEGIN 
			SET @result='kerst' 
		END
		ELSE
		BEGIN
			--2e pasen/hemelvaart/2e pinksteren
			SET @D2 = @yyyy % 19;
			SET @D3 = floor(@yyyy/100);
			SET @D4 = @yyyy % 100;
			SET @D5 = floor(@D3/4);
			SET @D7 = floor((8*@D3+13)/25);
			SET @D10= (19*@D2+@D3-@D5-@D7+15-floor((7*@D2+floor((11*(@D3-@D5-@D7)-4)/30)+6)/11)) % 29;
			SET @D13= (32+2*(@D3 % 4)+2*floor(@D4/4)-(@D4 % 4)-@D10) % 7;
			SET @D14= floor((90+@D10+@D13)/25);
			SET @D15= (19+@D10+@D13+@D14) % 32;
			SET @datum = DATEFROMPARTS( @yyyy, @D14, @D15 );
			IF @checkdatum = DATEADD(day,1,@datum)
			BEGIN 
				SET @result='pasen' 
			END;
			IF @checkdatum = DATEADD(day,39,@datum)
			BEGIN 
				SET @result='hemelvaart' 
			END;
			IF @checkdatum = DATEADD(day,1+49,@datum)
			BEGIN 
				SET @result='pinksteren' 
			END;
		END;
	END;
	RETURN @result;
END;
GO

Test:

with all_days as (
SELECT top 365 
   DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY object_id) -1, '20140101') as date1
  FROM sys.all_objects
  )
  select date1, dbo.Is_feestdag(date1) as speciaal
  from all_days
;