[SQL] 日期 - 基本應用

• 日期資料型態的預設格式

1. datetime：yyyy-mm-dd hh:mm:ss.mmm
2. smalldate：yyyy-mm-dd hh:mm:ss
3. date：yyyy-mm-dd

• 基底日期

以 datetime 來表示為 1900-01-01 00:00:00.000 有兩種表示方式

SELECT CAST(0 AS datetime)
SELECT CAST('' AS datetime)

• 搭配 REPLACE 應用

-- 利用 REPLACE 把 連接號（-）、空格和冒號（:）拿掉
SELECT
 REPLACE(
   REPLACE(
     REPLACE(CONVERT(varchar(20), GETDATE(), 120 ),'-','') -- 把連結號拿掉
   ,' ','') -- 把空格拿掉
 ,':','') -- 把冒號拿掉

• 搭配 DATEADD、DATEDIFF 應用

第一天日期計算：
邏輯 1. 用 DATEDIFF 計算從基底日期至欲計算日期，總共有多個年（月、日、季或週）。
邏輯 2. 用 DATEADD + 步驟一的計算值，就可以找到年（月、季或週）的第一天日期 或 當天的午夜時間。

-- 當年第一天
SELECT 
 DATEADD(yy,
            DATEDIFF(yy,'',GETDATE()) -- 邏輯 1 計算值
        ,'')

-- 當月第一天
SELECT 
 DATEADD(mm,
            DATEDIFF(mm,'',GETDATE()) -- 邏輯 1 計算值
        ,'') 

-- 當季第一天
SELECT 
 DATEADD(qq,
            DATEDIFF(qq,'',GETDATE()) -- 邏輯 1 計算值
        ,'')

-- 當週星期一日期
SELECT 
 DATEADD(wk,
            DATEDIFF(wk,'',GETDATE()) -- 邏輯 1 計算值
        ,'')

最後一天日期計算
邏輯 1. 同 第一天日期計算邏輯，先算出第一天日期
邏輯 2. 第一天日期 減 一天，即為最後一天日期

-- 上月最後一天
SELECT 
 DATEADD(dd,-1,                                         -- 減 1 天
               DATEADD(mm,DATEDIFF(mm,'',GETDATE()),'') -- 當月第一天日期
        )

-- 去年最後一天
SELECT 
 DATEADD(dd,-1,                                         -- 減 1 天
               DATEADD(yy,DATEDIFF(yy,'',GETDATE()),'') -- 當年第一天日期
        )

-- 本月最後一天
SELECT 
 DATEADD(dd,-1,                                             -- 減 1 天
               DATEADD(mm,DATEDIFF(mm,'',GETDATE()) + 1,'') -- 計算值 加 1 個月，找出下個月第一天
        )

-- 本年最後一天
SELECT 
 DATEADD(dd,-1,                                             -- 減 1 天
               DATEADD(yy,DATEDIFF(yy,'',GETDATE()) + 1,'') -- 計算值 加 1 年，找出明年第一天
 )

• 當天午夜時間

-- 方法一：搭配 DATEADD、DATEDIFF 應用
SELECT
 DATEADD
    (dd,
        DATEDIFF(dd,'',GETDATE())
    ,'')

-- 方法二：利用轉換資料形態
SELECT 
 CAST
    (
      CONVERT(char(10),GETDATE(),120) -- 把 GETDATE() 轉為字串，因限定字元因素，只會產生 yyyy-mm-dd
      AS Datetime
    ) -- 再把 yyyy-mm-dd 轉為 datetime 變成午夜時間

• 每個月的最後一天

-- 方法一：搭配 DATEADD、DATEDIFF 應用
SELECT 
 DATEADD(dd,-1,                                             
               DATEADD(mm,DATEDIFF(mm,'',GETDATE()) + 1,'') 
        )

-- 方法二：利用 2012 提供的新函數 EOMONTH
--         EOMONTH 回傳值為 date 資料型態
SELECT EOMONTH(GETDATE())

延伸閱讀
• [SQL] 當天午夜時間

參考資料
• SQL SERVER – Multiple ways to get Date and Time separately from DateTime