TSQL 选择年和月范围内的数据

Question

我正在尝试查询开始年份和月份以及结束年份和月份范围内的数据。但是SQL返回所选的年份和月份。任何人都可以用我的方法确定问题。

谢谢！

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

您需要将

获得的 int 值转换为日期值。

在 Sql Server 2012 或更高版本中，可以使用内置函数DATEFROMPARTS执行此操作：

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

如果您使用的是早期版本的 sql server，则需要生成一个表示日期的字符串（使用 iso 格式 yyyy-mm-dd），然后将其强制转换为日期：

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))