[SQL] DATEDIFF 回傳值

論壇問題，利用 DATEDIFF() 計算兩個時間區間秒數時，發生下列的錯誤訊息

DATEDIFF 函數造成溢位。分隔兩個日期/時間執行個體的日期部分數目太大。請嘗試使用日期部分較不精確的 DATEDIFF

發生原因：
DATEDIFF() 回傳值為 int，範圍為 -2,147,483,648 到 +2,147,483,647，換算成秒的話，最大差異是 68 年，因此起始日期和結束日期超過 68 年，就會出現上述的錯誤訊息。

解決方法：

DECLARE @Temp TABLE (StartDate datetime,EndDate datetime)
INSERT INTO @Temp VALUES 
  ('1900-05-07 23:59:59','2014-05-07 13:23:10') , -- 秒數破表
  ('2014-05-06 16:13:45','2014-05-07 20:13:37') , 
  ('2014-05-06 20:13:32','2014-05-07 03:23:55') , 
  ('2014-05-07 05:00:11','2014-05-07 07:10:11')   -- 下面說明用此資料當成範例

SELECT
  T.StartDate ,
  T.EndDate ,
  FORMAT -- 閱讀方便，沒有計算上的意義
      (
          -- Step2：數學邏輯運算
          CAST(DATEDIFF(mi,T.S,T.E) AS bigint) * 60 -- Step2-1
              - DATEDIFF(ss,T.S,T.StartDate)        -- Step2-2
              + DATEDIFF(ss,T.E,EndDate) ,          -- Step2-3
          '###,###,###'
      ) AS TotalSec
FROM
  (
      SELECT  
        StartDate ,
        EndDate ,
　　　　-- Step1：捨去秒數
        CAST(CONVERT(char(16),StartDate,120) AS datetime) AS S ,
        CAST(CONVERT(char(16),EndDate,120) AS datetime) AS E
      FROM @Temp
  ) AS T

• Step1：利用 CONVERT() 把日期轉為ODBC標準(120)的字元日期，並利用 char(16) 限定字元，來進行捨去秒數的行為，起始日期捨去秒數為 S、結束日期捨去秒數為 E。
• Step2：
1. 計算 S 和 E 相差多少分鐘，而 1 分鐘 = 60 秒，就可以計算出多少秒數，也避開 DATEDIFF() 回傳值的限制
2. 起始日期捨去秒，秒數變多，必須把它扣掉，EX：05:00:11 => 05:00:00，減 11 秒
3. 結束日期捨去秒，秒數變少，必須把它加回，EX：07:10:11 => 07:10:00，加 11 秒

下圖是把 Step2 圖表化，起始日期和結束日期相差秒數（黃色線）= S、E 秒數差（藍色線）- 起始日期和 S 秒數差（綠色） + 結束日期 和 E 秒數差（褐色線）

上述語法捨去秒數邏輯，是想出來的第三種，紀錄一下第一、第二種和其無法應用的原因

邏輯一：直接轉為 smalldatetime 藉由精確度只到分特性，把秒數給捨去
問題點：datetime 轉為 smalldatetime 時，大於等於 30 秒的話，會進位　

DECLARE @Temp TABLE (Data datetime)
INSERT INTO @Temp VALUES 
  ('2014-05-07 15:49:29') ,
  ('2014-05-07 15:49:30') ,
  ('2014-05-07 15:49:31')

SELECT 
  CAST(Data AS smalldatetime)
FROM @Temp

邏輯二：直接轉字串來捨去秒，再轉回 datetime
問題點：沒有辦法區分是否為 24 小時制

DECLARE @Temp TABLE (Data datetime)
INSERT INTO @Temp VALUES 
  ('2014-05-07 08:13:37') ,
  ('2014-05-07 20:13:37')

SELECT 
  Data , 
  CAST(CAST(Data AS char(16)) AS datetime)  AS DataConvert
FROM @Temp

參考資料
• 論壇問題出處
• DATEDIFF()