sql - 如何按周获取数据库值

Question

我只想问一下如何实现按周取数据库的记录？使用 SQL我真正的意思是当我输入 1 时它会在一个月的第一周获得记录我今天做了很多研究，但似乎找不到好的解决方案

代码如下:

DECLARE @MONTH int
DECLARE @YEAR int
DECLARE @WEEK int

SET @MONTH = 9
SET @YEAR = 2013
SET @WEEK = 1

SELECT RH.RepairOrderNumber FROM dbo.RepairOrderHeader RH
WHERE MONTH(RH.DateReleased) = @MONTH AND YEAR(RH.DateReleased) = @YEAR AND WEEK(RH.DateReleased) = @WEEK

我只想按月、年和周获取记录，有什么方法和精确的代码可以做到这一点吗？

Answer 1

DATEPART(WEEK,) 为您提供一年中的一周数，因此您只需计算该月的周数，计算当前日期周数与第一周数之间的差值每月的第几天:

WHERE MONTH(RH.DateReleased) = @MONTH 
  AND YEAR(RH.DateReleased) = @YEAR 
  AND DATEPART(WEEK,RH.DateReleased)
       -DATEPART(WEEK,DATEADD(DAY,-DAY(RH.DateReleased)+1,RH.DateReleased))+1 
       = @WEEK