德瑞克：SQL Server 學習筆記: SQL Server 2012：EOMONTH 日期函數，傳回指定日期的當月最後一天

2013-01-22

SQL Server 2012：EOMONTH 日期函數，傳回指定日期的當月最後一天

在 Excel 上的 EOMONTH 日期函數，可以用來傳回在 start_date 之前或之後指定月數的這一個月最後一天的序列值。

使用 EOMONTH 來計算剛好落在這一個月最後一天的到期日。

如今，在 SQL Server 2012 版本也提供此函數。

EOMONTH (Transact-SQL)
以選擇性位移，傳回包含指定日期的當月最後一天。

SQL Server 2012 版本新增加。

語法：

EOMONTH ( start_date [, month_to_add ] )

引數：
(1) start_date
日期運算式為當月最後一天該傳回者指定日期。

(2) month_to_add
指定要加入 start_date 之月數的選擇性整數運算式。

如果這個引數是指定的，則 EOMONTH 新增當月指定數字到 start_date，還有傳回當月最後一天做為結果日期。
如果這個加法溢位有效日期範圍，則會引起錯誤。

傳回類型：date

--
備註
此函數支援啟動遠端功能到 SQL Server 2012 伺服器和上方。
其無法從遠端處理到低於 SQL Server 2012 的伺服器版本。

請參考以下的範例程式碼：

EX1. 日期時間類型與 EOMONTH 函數

-- EX1. 日期時間類型與 EOMONTH 函數
-- 傳回包含指定日期的當月最後一天
DECLARE @date DATETIME2

SET @date = '20130201'
SELECT EOMONTH ( @date ) AS N'指定日期的當月最後一天'  -- 2013-02-28
GO

-- 01_日期時間類型與 EOMONTH 函數

EX2. 字串參數及明確轉換與 EOMONTH 函數

-- EX2. 字串參數及明確轉換 與 EOMONTH 函數
-- 傳回包含指定日期的當月最後一天
DECLARE @date VARCHAR(255)

SET @date = '20140201'
SELECT EOMONTH ( @date ) AS N'指定日期的當月最後一天'  -- 2014-02-28
GO

-- 02_字串參數及明確轉換與 EOMONTH 函數

EX3. 具有和不具有 month_to_add 參數的 EOMONTH 函數

-- EX3. 具有和不具有 month_to_add 參數的 EOMONTH 函數
-- 計算：以目前日期為基礎，其相差特定月數的最後一天

DECLARE @date DATETIME2
SET @date = SYSDATETIME()

SELECT EOMONTH ( @date ) AS N'本月最後一天' , @date N'今天'
SELECT EOMONTH ( @date, 1 ) AS N'下一個月最後一天' , @date N'今天'

SELECT EOMONTH ( @date, -1 ) AS N'前一個月最後一天' , @date N'今天'
SELECT EOMONTH ( @date, 2 ) AS N'下兩個月最後一天' , @date N'今天'

SELECT EOMONTH ( @date, -2 ) AS N'前兩個月最後一天' , @date N'今天'
GO

-- 03_計算：以目前日期為基礎，其相差特定月數的最後一天

在 SQL Server 2012 之前版本的作法：

DATEADD (Transact-SQL)
傳回指定的 date，並將指定的 number 間隔 (帶正負號的整數) 加入至該 date 的指定 datepart。

語法：

DATEADD (datepart , number , date )

引數：
(1) datepart
這是 integernumber 要加入其中的 date 部分。

(2) number
這是可解析成 int (要加入至 date 的 datepart) 的運算式。
使用者自訂的變數有效。

如果您指定了含有十進位小數的值，該小數就會被截斷而且不會四捨五入。

(3) date
這是可解析成 time、date、smalldatetime、datetime、datetime2 或 datetimeoffset 值的運算式。
date 可以是運算式、資料行運算式、使用者自訂變數或字串常值。

如果此運算式為字串常值，它必須解析為 datetime。
若要避免模糊不清，請使用四位數年份。

--
傳回資料類型：
是 date 引數的資料類型，但字串常值除外。
字串常值的傳回資料類型是 datetime。

如果字串常值的秒數小數位數超過三個位置 (. nnn)，或者包含時區時差部分，就會引發錯誤。

--
關於 datepart 引數

dayofyear 、day 和 weekday 都會傳回相同的值。

每個 datepart 及其縮寫都會傳回相同的值。

如果 datepart 是 month、date 月份的天數比傳回月份的天數多，而且 date 日期不存在傳回月份中，就會傳回傳回月份的最後一天。

例如，九月有 30 天。因此，下列陳述式會傳回 2006-09-30 00:00:00.000：

SELECT DATEADD(month, 1, '2006-08-30')
SELECT DATEADD(month, 1, '2006-08-31')

--
在 SQL Server 2012 之前版本的作法，例如：2000、2005、2008、2008 R2

需求：
計算與目前日期為基礎，其相差特定月數的最後一天

公式：

SELECT DATEADD(MM, DATEDIFF(MM, -1, GETDATE()) + <相差特定月數>, 0) - 1 AS N'xxx月最後一天'

請參考以下的範例程式碼：

DECLARE @date DATETIME
SET @date = GETDATE()

SELECT DATEADD(MM, DATEDIFF(MM, -1, @date), 0) - 1 AS N'本月最後一天' , @date N'今天'
SELECT DATEADD(MM, DATEDIFF(MM, -1, @date) - 1, 0) - 1 AS N'前一個月最後一天' , @date N'今天'

SELECT DATEADD(MM, DATEDIFF(MM, -1, @date) + 1, 0) - 1 AS  N'下一個月最後一天' , @date N'今天'
SELECT DATEADD(MM, DATEDIFF(MM, -1, @date) - 2, 0) - 1 AS N'前兩個月最後一天' , @date N'今天'

SELECT DATEADD(MM, DATEDIFF(MM, -1, @date) + 2, 0) - 1 AS  N'下兩個月最後一天' , @date N'今天'
GO

-- 04_舊版_計算與目前日期為基礎，其相差特定月數的最後一天