在 SQL 中获取当月的最后一天

Question

我需要将一个月的最后一天作为 SQL 中的日期给出。如果我有一个月的第一天，我可以这样做：

 DATEADD(DAY, DATEADD(MONTH,'2009-05-01',1), -1)

但是有谁知道如何概括它，以便我可以找到任何给定日期的一个月的最后一天？

原文由 Byron Whitlock 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

这是我的版本。无需字符串操作或强制转换，只需调用 DATEADD 、 YEAR 和 MONTH 函数：

 DECLARE @test DATETIME
SET @test = GETDATE()  -- or any other date

SELECT DATEADD(month, ((YEAR(@test) - 1900) * 12) + MONTH(@test), -1)

原文由 LukeH 发布，翻译遵循 CC BY-SA 2.5 许可协议

Answer 2

–## 有用的日期函数

选择

GETDATE() AS [DateTime],

CAST(GETDATE() AS DATE) AS [Date],

DAY(GETDATE()) AS [Day of Month],

FORMAT(GETDATE(),'MMMM') AS [Month Name],

FORMAT(GETDATE(),'MMM') AS [Month Short Name],

FORMAT(GETDATE(),'MM') AS [Month No],

YEAR(GETDATE()) AS [Year],

CAST(DATEADD(DD,-(DAY(GETDATE())-1),GETDATE()) AS DATE) AS [Month Start Date],

EOMONTH(GETDATE()) AS [Month End Date],

CAST(DATEADD(M,-1,DATEADD(MM, DATEDIFF(M,0,GETDATE()),0)) AS DATE) AS [Previous Month Start Date],

CAST(DATEADD(S,-1,DATEADD(MM, DATEDIFF(M,0,GETDATE()),0)) AS DATE) AS [Previous Month End Date],

CAST(DATEADD(M,+1,DATEADD(MM, DATEDIFF(M,0,GETDATE()),0)) AS DATE) AS [Next Month Start Date],

CAST(DATEADD(D,-1,DATEADD(MM, DATEDIFF(M,0,GETDATE())+2,0)) AS DATE) AS [Next Month End Date],

CAST(DATEADD(WW, DATEDIFF(WW,0,GETDATE()),0) AS DATE) AS [First Day of Current Week],

CAST(DATEADD(WW, DATEDIFF(WW,0,GETDATE())+1,-1) AS DATE) AS [Last Day of Current Week],

CAST(DATEADD(WW, DATEDIFF(WW,0,GETDATE())-1,0) AS DATE) AS [First Day of Last Week],

CAST(DATEADD(WW, DATEDIFF(WW,0,GETDATE()),-1) AS DATE) AS [Last Day of Last Week],

CAST(DATEADD(WW, DATEDIFF(WW,0,GETDATE())+1,0) AS DATE) AS [First Day of Next Week],

CAST(DATEADD(WW, DATEDIFF(WW,0,GETDATE())+2,-1) AS DATE) AS [Last Day of Next Week]

原文由 ianhollo 发布，翻译遵循 CC BY-SA 4.0 许可协议