TSQL SELECT data within range of year and month

Question

I'm trying to query data within a range of start year and month and end year and month. But SQL returnes onty the year and the month chosen. Can anyone identify the problem with my approach.

Thanks!

ALTER PROCEDURE xxx 
(@JaarBegin AS int
, @JaarEind AS int
, @MaandBegin AS int
, @MaandEind AS int)

AS
BEGIN

WITH 
CTE AS
(
SELECT [D_Medewerker_ID]
      ,[Gebruikersnaam]
      ,[Naam]
      ,[Afdelingscode]
      ,CONVERT(date, [Datum_uit_dienst]) AS DatumIn
      ,CONVERT(date, [Datum_in_dienst]) AS DatumUit
FROM [DM].[dm].[D_Medewerker] AS M
), 
CTE2 AS(
 SELECT F.[D_Functie_ID] 
      ,[Generieke_Functie] 
      ,[Specifieke_Functie] 
      ,Fo.[D_Medewerker_ID]
  FROM [DM].[dm].[D_Functie] AS F
  JOIN dm.dm.F_FormatieBezetting AS Fo
  ON F.D_Functie_ID = Fo.D_Functie_ID
  )

SELECT DISTINCT CTE.[Gebruikersnaam]
, CTE.Naam
, CTE.Afdelingscode
, CTE.DatumIn
, CTE.DatumUit
, CTE2.Generieke_Functie
, CTE2.Specifieke_Functie
FROM CTE 
JOIN CTE2 
ON CTE.D_Medewerker_ID = CTE2.D_Medewerker_ID
WHERE DATEPART(year,CTE.DatumUit) BETWEEN @JaarBegin AND @JaarEind
AND DATEPART(MONTH, CTE.DatumUit) >= @MaandBegin AND DATEPART(MONTH, CTE.DatumUit) <= @MaandEind
ORDER BY CTE.DatumUit DESC;
END

Answer 1

You need to convert the int values you get to a date value.

In Sql server 2012 or later, you can use the built-in function DATEFROMPARTS to do this:

WHERE CTE.DatumUit >= DATEFROMPARTS ( @JaarBegin , @MaandBegin , 1 )  
AND CTE.DatumUit < DATEADD(MONTH, 1, DATEFROMPARTS ( @JaarEind , @MaandBegin , 1 ))

If you are working with an earlier version of sql server, you need to build a string that represents the date (using iso format yyyy-mm-dd) and then cast it to date:

WHERE CTE.DatumUit >= CAST(RIGHT('0000' + CAST(@JaarBegin as varchar(4)), 4) + '-' + RIGHT('00' + CAST(@MaandBegin as varchar(2)), 2) +'-01' as datetime)
AND CTE.DatumUit < DATEADD(MONTH, 1, CAST(RIGHT('0000' +CAST(@JaarEind as varchar(4)), 4) + '-' + RIGHT('00' + CAST(@MaandBegin as varchar(2)), 2) +'-01' as datetime))