sql - 查找不在该月第一天或最后一天的日期

Question

我有一个名为 Locations 的表，其中包含名为 effective_date 的列，其中包含多年以来的许多日期，并且我只想检索那些不是该月的第一天或该月的最后一天。

Answer 1

这是一个SQL Fiddle Demo详细信息如下。

生成一个表和一些示例测试数据:

CREATE TABLE Locations(
   effective_date DATETIME
)

INSERT INTO Locations
  VALUES('2014-01-01') -- First day so we would expect this NOT to be returned
INSERT INTO Locations
  VALUES('2014-01-02') -- This should be returned
INSERT INTO Locations
  VALUES('2014-01-31') -- Last day of January so this should NOT be returned

然后，下面的查询计算出表中每个日期的该月的最后一天，仅当 effective_date 不是计算得出的该月的第一天或最后一天时才返回记录。

SELECT effective_date FROM Locations
 WHERE -- not the first day (the easy bit!)
   DATEPART(day, effective_date) <> 1 
       -- not the last day (slightly more complex)
   AND DATEPART(day, effective_date) <> 
       DATEPART(day, DATEADD(second,-1,DATEADD(month, DATEDIFF(month,0,effective_date)+1,0)))

仅执行时 January, 02 2014 00:00:00+0000 会按预期返回。

这里的巧妙之处是在给定日期时计算当月最后一天的函数，让我们检查一下并分解它:

DECLARE @sampleDate DATETIME
SET @sampleDate = '2014-01-02'

-- Calculate the number of months between '1900-01-01' and the @sampleDate
-- +1 as we want to shift into the following month so we can work back:
SELECT DATEDIFF(month,0,@sampleDate) + 1
-- Result --> 1369

-- Create a new date by adding the result of the previous step in
--  months to '1900-01-01'
SELECT DATEADD(month, DATEDIFF(month,0,@sampleDate)+1,0)
-- Result --> '2014-02-01' (giving first day of the following month)

-- Subtract one second from this
SELECT DATEADD(second,-1,DATEADD(month, DATEDIFF(month,0,@sampleDate)+1,0))
-- Result --> '2014-01-31 23:59:59' (giving the very end of the original month) 

-- Finally extract the day of the month
SELECT DATEPART(day, DATEADD(second,-1,DATEADD(month, DATEDIFF(month,0,@sampleDate)+1,0)))
-- Result --> 31