526互联

SQL SERVER不使用EOMONTH,获取月份的最后一天- SQL server 2008

发布时间 2023-03-27 12:09:31作者: po-A
1) To find the last date of the current month using EOMONTH 
DECLARE @current_date DATE = GETDATE()
SELECT EOMONTH (@current_date, 0) AS LastDayOfCurrentMonth
NEXT MONTH:
DECLARE @current_date DATE = GETDATE()
SELECT EOMONTH (@current_date, 1 ) AS LastDayOfNextMonth

2）SQL server 2005, SQL server 2008 （SQL SERVER不使用EOMONTH,获取月份的最后一天）
-- OLD Version
DECLARE @dt DATE
SET @dt = GETDATE()
SELECT CAST(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dt)+1,0)) AS DATE)as LastDayOfCurrentMonth
-- OLD Version( SQL server 2005, SQL server 2008)
DECLARE @dt DATE
SET @dt = GETDATE() --假设为2023年3月27日
SELECT CAST(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dt)+1,0)) AS DATE)as LastDayOfCurrentMonth
SELECT DATEADD( dd,-1,DATEADD(mm, DATEDIFF(m,0,@dt)+1,0)  ) as LastDayOfCurrentMonth_MinusDay_GetEndOfPreviousMonth
SELECT DATEADD(mm, DATEDIFF(m,0,@dt)+1,0)   as LastDayOfCurrentMonth_Dateaddmm_ChangedToDateFormat
SELECT DATEDIFF(m,0,@dt)+1  as LastDayOfCurrentMonth_DateDiff_GetNextMonth
SELECT DATEDIFF(m,0,@dt)  as LastDayOfCurrentMonth_DateDiff
SELECT @dt as LastDayOfCurrentMonth_GETDATE--1900年~2023年的3月份序号为1478
SELECT CAST(1479.00/12 AS decimal(18,2))  --123.25个年

分步骤解析：