日期和時間類型
https://docs.microsoft.com/zh-tw/sql/t-sql/functions/date-and-time-data-types-and-functions-transact-sql?view=sql-server-2017
https://msdn.microsoft.com/zh-tw/library/ms189794(v=sql.120).aspx
DATEDIFF ( datepart , startdate , enddate )
- datepart
這是指定所跨越之界限類型的 startdate 和 enddate 部分。 下表列出所有有效的 datepart 引數。 使用者自訂變數對等項目無效。
datepart
|
縮寫
|
|---|
year
|
yy, yyyy
|
quarter
|
qq, q
|
month
|
mm, m
|
dayofyear
|
dy, y
|
day
|
dd, d
|
week
|
wk, ww
|
hour
|
hh
|
minute
|
mi, n
|
second
|
ss, s
|
millisecond
|
ms
|
microsecond
|
mcs
|
nanosecond
|
ns
|
- startdate
這是可解析成 time、date、smalldatetime、datetime、datetime2 或 datetimeoffset 值的運算式。 date 可以是運算式、資料行運算式、使用者自訂變數或字串常值。 從 enddate 中扣除 startdate。
- enddate
請參閱＜startdate＞。
善用DATEADD和DATEDIFF
DATEDIFF 函数造成溢位
DATEDIFF 函數造成溢位。分隔兩個日期/時間執行個體的日期部分數目太大。請嘗試使用日期部分較不精確的 DATEDIFF。
摘要:SQL Server - DATEDIFF 函數造成溢位。分隔兩個日期/時間執行個體的日期部分數目太大。請嘗試使用日期部分較不精確的 DATEDIFF。
因為 兩個日期之間差距了100年。
而導致用秒去算的時後發生了錯誤。
select DATEDIFF (second, StartDate, SysDateTime()) from TableName
為了解決這個問題從網路上找到了解答，如下
create function fn_diffsecond
(
@date1 datetime,
@date2 datetime
)
returns bigint
as
begin
return (convert(bigint, datediff(day, @date1, @date2)) * 24 * 60 * 60)
- (datediff(second, dateadd(day, datediff(day, 0, @date1), 0), @date1))
+ (datediff(second, dateadd(day, datediff(day, 0, @date2), 0), @date2))
end
go
select dbo.fn_diffsecond('1900-01-02 03:45:56', '9999-12-31 23:59:59')
select
(convert(bigint, datediff(day, [StartDate] , SysDateTime())) * 24 * 60 * 60)
- (datediff(second, dateadd(day, datediff(day, 0, [StartDate]), 0), [StartDate]))
+ (datediff(second, dateadd(day, datediff(day, 0, SysDateTime()), 0), SysDateTime()))
from TableName
在 Excel 上的 EOMONTH 日期函數，可以用來傳回在 start_date 之前或之後指定月數的這一個月最後一天的序列值。
使用 EOMONTH 來計算剛好落在這一個月最後一天的到期日。
如今，在 SQL Server 2012 版本也提供此函數。
EOMONTH (Transact-SQL)
以選擇性位移，傳回包含指定日期的當月最後一天。
SQL Server 2012 版本新增加。
語法：
1
|
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 函數
1
2
3
4
5
6
7
|
DECLARE @date DATETIME2
SET @date = '20130201'
SELECT EOMONTH ( @date ) AS N'指定日期的當月最後一天'
GO
|
-- 01_日期時間類型與 EOMONTH 函數
EX2. 字串參數及明確轉換 與 EOMONTH 函數
1
2
3
4
5
6
7
|
DECLARE @date VARCHAR(255)
SET @date = '20140201'
SELECT EOMONTH ( @date ) AS N'指定日期的當月最後一天'
GO
|
-- 02_字串參數及明確轉換 與 EOMONTH 函數
EX3. 具有和不具有 month_to_add 參數的 EOMONTH 函數
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
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。
語法：
1
|
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：
1
2
|
SELECT DATEADD(month, 1, '2006-08-30')
SELECT DATEADD(month, 1, '2006-08-31')
|
--
在 SQL Server 2012 之前版本的作法，例如：2000、2005、2008、2008 R2
需求：
計算與目前日期為基礎，其相差特定月數的最後一天
公式：
1
|
SELECT DATEADD(MM, DATEDIFF(MM, -1, GETDATE()) + <相差特定月數>, 0) - 1 AS N'xxx月最後一天'
|
請參考以下的範例程式碼：
1
2
3
4
5
6
7
8
9
10
11
|
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
沒有留言:
張貼留言