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
Win
