商帖小記 SomeTips: 3月 2022

SQL語法--如何取得日期及計算日期？

SQL取得日期需要透過特別的table，例如ORACLE資料庫要訪問DUAL table；DB2資料庫要訪問SYSDUMMY1 table。

以下以DB2為例，說明SYSDUMMY1 table及日期取得及計算的方法及範例：

SYSDUMMY1說明：

SYSDUMMY1是屬於系統內建的table，位於SYSIBM下，提供給SQL語句需要時使用，它的內容並不重要(從以下的例子可以發現)。
我們只要利用SQL指令 SELECT * FROM SYSIBM.SYSDUMMY1 就可以看到它的內容。我們可以發現它的內容只有一筆資料，一個欄位，欄位名稱為IBMREQD其值為Y。

取得各種形式的時間(如DATE, TIME, YEAR...)，以目前的時間為例：

SELECT
  -- DATE & TIME
  CURRENT TIMESTAMP AS TIMESTAMP,
  CURRENT date AS DATE,
  CURRENT time AS TIME,
  -- YEAR
  YEAR(CURRENT TIMESTAMP) AS YEAR,
  -- MONTH
  MONTH(CURRENT TIMESTAMP) AS MONTH,
  MONTHNAME(CURRENT TIMESTAMP) AS MONTHNAME,     -- 傳回January, Febuary,...
  -- DAY
  DAY(CURRENT TIMESTAMP) AS DAY,                 -- 傳回本月的第幾天
  DAYS(CURRENT TIMESTAMP) AS DAYS,               -- 傳回自西元0001-1-1至此日期的天數
  DAYOFYEAR(CURRENT TIMESTAMP) AS DAYOFYEAR,     -- 傳回本年度的第幾天
  -- HOUR
  HOUR(CURRENT TIMESTAMP) AS HOUR,
  -- MINUTE
  MINUTE(CURRENT TIMESTAMP) AS MINUTE,
  -- SECOND
  SECOND(CURRENT TIMESTAMP) AS SECOND,
  MIDNIGHT_SECONDS(CURRENT TIMESTAMP) AS MIDNIGHT_SECONDS,-- 傳回當天零時到此時間的總秒數
  MICROSECOND(CURRENT TIMESTAMP) AS MICROSECOND,
  -- WEEK
  DAYNAME(CURRENT TIMESTAMP) AS DAYNAME,               -- 傳回Sunday, Monday,...
  DAYOFWEEK_ISO(CURRENT TIMESTAMP) AS DAYOFWEEK_ISO,   -- 傳回1到7, 週一為1，依此類推.
  DAYOFWEEK(CURRENT TIMESTAMP) AS DAYOFWEEK,           -- 傳回1到7, 週日為1，依此類推.
  WEEK_ISO(CURRENT TIMESTAMP) AS WEEK_ISO,             -- 傳回本年度的第幾週(1~53)
  WEEK(CURRENT TIMESTAMP) AS WEEK                      -- 傳回本年度的第幾週(1~54)
FROM SYSIBM.SYSDUMMY1

取得各種形式的時間，執行結果如下：

將字串轉換為日期的各種形式：

SELECT 
  DATE ('2022-03-15') AS DATE1,
  DATE ('03/15/2022') AS DATE2,
  TIME ('12:15:10') AS TIME1,
  TIME ('12.15.10') AS TIME2, 
  TIMESTAMP ('2022-03-15-12.00.00.000000') AS TIMESTAMP1,
  TIMESTAMP ('2022-03-15 12:00:00') AS TIMESTAMP2
FROM SYSIBM.SYSDUMMY1

將字串轉換為日期，執行結果如下：

日期計算的各種方法：

SELECT
  DAYS (CURRENT DATE) - DAYS (DATE('2022-03-10')) AS DAYS1,
  DATE('2022-03-15') - date('2022-03-10') AS DAYS2,
  DATE('2022-03-15') + 2 YEAR AS ADD_YEAR,
  DATE('2022-03-15') + 2 YEAR + 2 MONTH + 2 DAY AS ADD_YEAR_MONTH_DAY,
  TIME('12:15:10') + 2 HOURS + 2 MINUTES + 2 SECONDS AS ADD_HOUR_MINUTE_SEC
FROM SYSIBM.SYSDUMMY1