搜尋本站文章

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_舊版_計算與目前日期為基礎,其相差特定月數的最後一天






參考資料:

EOMONTH
http://office.microsoft.com/zh-tw/excel-help/HP005209076.aspx

EOMONTH (Transact-SQL)
http://technet.microsoft.com/zh-tw/library/hh213020.aspx