[SQL Sever] 日期時間
SQL Server 中，有這些型態來表示日期：
DATE型態 - 格式是 YYYY-MM-DDDATETIME型態 - 格式是 YYYY-MM-DD HH:MI:SSSMALLDATETIME型態 - 格式是 YYYY-MM-DD HH:MI:SS
SQL Server
- GETDATE() 取得現在的日期時間
- DATEPART() 取出日期時間中特定的部分
- DATEADD() 增加或減少指定的時間間隔
- DATEDIFF() 日期相減
- CONVERT() 格式化日期時間顯示
SQL Server GETDATE() 取得現在的日期時間
在 SQL Server 你可以使用 GETDATE() 函數來取得當前的日期時間。
GETDATE() 語法 (Syntax)
GETDATE()
GETDATE() 用法 (Example)
這個 SQL：
SELECT GETDATE() AS CurrentDateTime
會得到現在的日期時間例如：
2018-11-30 13:10:02.047SQL Server DATEPART() 取出日期和時間中特定的部分
在 SQL Server 中，我們可以用 DATEPART() 函數來取出日期和時間中特定的部分，比如年、月、日、時、分、秒等。
DATEPART() 語法 (Syntax)
DATEPART(datepart , date)
DATEPART() 會返回一個整數，其中 datepart 參數用來指定要返回的部分，datepart 可以是這些值：
| datepart (全名和縮寫) | 返回值說明 |
|---|---|
| year, yyyy, yy | 年，例如 2007 |
| month, mm, m | 月，例如 10 |
| day, dd, d | 日，例如 30 |
| hour, hh | 時，例如 12 |
| minute, n | 分，例如 15 |
| second, ss, s | 秒，例如 32 |
| millisecond, ms | 毫秒，例如 123 |
| microsecond, mcs | 微秒，例如 123456 |
| nanosecond, ns | 毫微秒，例如 123456700 |
| quarter, qq, q | 季，例如 4 |
| dayofyear, dy, y | 一年中的第幾天，例如 303 |
| week, wk, ww | 一年中的第幾週，例如 45 |
| weekday, dw | 星期幾，例如 1 |
| TZoffset, tz | 時區 time zone offset，單位是分鐘，例如 310 |
DATEPART() 用法 (Example)
假設有一個 Orders table:
| OrderId | ProductName | OrderDate |
|---|---|---|
| 1 | 'Bike' | 2018-10-22 16:25:46.635 |
這個 SQL：
SELECT DATEPART(yyyy, OrderDate) AS OrderYear,
DATEPART(mm, OrderDate) AS OrderMonth,
DATEPART(dd, OrderDate) AS OrderDay
FROM Orders
會得到例如：
| OrderYear | OrderMonth | OrderDay |
|---|---|---|
| 2018 | 10 | 22 |
SQL Server DATEADD() 增加或減少指定的時間間隔
在 SQL Server 中，我們可以用 DATEADD() 函數在日期和時間中增加或減少指定的時間間隔。
DATEADD() 語法 (Syntax)
DATEADD(datepart, number, date)
DATEADD() 會將指定的 number 值 (一個正負號的整數) 加到 date 值的指定 datepart，然後返回修改後的值。
其中 datepart 可以是這些值：
| datepart (全名和縮寫) | 說明 |
|---|---|
| year, yyyy, yy | 年 |
| month, mm, m | 月 |
| day, dd, d | 日 |
| hour, hh | 時 |
| minute, n | 分 |
| second, ss, s | 秒 |
| millisecond, ms | 毫秒 |
| microsecond, mcs | 微秒 |
| nanosecond, ns | 毫微秒 |
| quarter, qq, q | 季 |
| dayofyear, dy, y | 一年中的第幾天 |
| week, wk, ww | 週 |
| weekday, dw | 星期幾 |
DATEADD() 用法 (Example)
假設有一個 Orders table:
| OrderId | ProductName | OrderDate |
|---|---|---|
| 1 | 'Bike' | 2018-10-22 16:25:46.635 |
對 OrderDate 往後加兩天：
SELECT OrderId,
DATEADD(day, 2, OrderDate) AS OrderDate2
FROM Orders
會得到例如：
| OrderId | OrderDate2 |
|---|---|
| 1 | 2018-10-24 16:25:46.635 |
舉另外一個例子，這個 SQL：
DECLARE @datetime2 datetime2 = '2007-01-01 13:10:10.1111111';
SELECT 'year', DATEADD(year, 1, @datetime2)
UNION ALL
SELECT 'quarter', DATEADD(quarter, 1, @datetime2)
UNION ALL
SELECT 'month', DATEADD(month, 1, @datetime2)
UNION ALL
SELECT 'dayofyear', DATEADD(dayofyear, 1, @datetime2)
UNION ALL
SELECT 'day', DATEADD(day, 1, @datetime2)
UNION ALL
SELECT 'week', DATEADD(week, 1, @datetime2)
UNION ALL
SELECT 'weekday', DATEADD(weekday, 1, @datetime2)
UNION ALL
SELECT 'hour', DATEADD(hour, 1, @datetime2)
UNION ALL
SELECT 'minute', DATEADD(minute, 1, @datetime2)
UNION ALL
SELECT 'second', DATEADD(second, 1, @datetime2)
UNION ALL
SELECT 'millisecond', DATEADD(millisecond, 1, @datetime2)
UNION ALL
SELECT 'microsecond', DATEADD(microsecond, 1, @datetime2)
UNION ALL
SELECT 'nanosecond', DATEADD(nanosecond, 1, @datetime2);
會得到以下結果：
Year 2008-01-01 13:10:10.1111111
quarter 2007-04-01 13:10:10.1111111
month 2007-02-01 13:10:10.1111111
dayofyear 2007-01-02 13:10:10.1111111
day 2007-01-02 13:10:10.1111111
week 2007-01-08 13:10:10.1111111
weekday 2007-01-02 13:10:10.1111111
hour 2007-01-01 14:10:10.1111111
minute 2007-01-01 13:11:10.1111111
second 2007-01-01 13:10:11.1111111
millisecond 2007-01-01 13:10:10.1121111
microsecond 2007-01-01 13:10:10.1111121
nanosecond 2007-01-01 13:10:10.1111111 SQL Server DATEDIFF() 日期相減
在 SQL Server 中，我們可以用 DATEDIFF() 函數取得兩個日期之間相差的時間間隔。
DATEDIFF() 語法 (Syntax)
DATEDIFF(datepart, startdate, enddate)
DATEDIFF() 執行後會返回一個正負樹的整數，表示
enddate - startdate 的時間間隔，datepart 參數則用來指定返回值的單位。
datepart 可以是：
| datepart (全名和縮寫) | 說明 |
|---|---|
| year, yyyy, yy | 年 |
| month, mm, m | 月 |
| day, dd, d | 日 |
| hour, hh | 時 |
| minute, n | 分 |
| second, ss, s | 秒 |
| millisecond, ms | 毫秒 |
| microsecond, mcs | 微秒 |
| nanosecond, ns | 毫微秒 |
| quarter, qq, q | 季 |
| dayofyear, dy, y | 一年中的第幾天 |
| week, wk, ww | 週 |
DATEDIFF() 用法 (Example)
取得兩個日期之間差幾天：
SELECT DATEDIFF(day, '2008-06-10', '2008-08-10') AS DiffDate
結果：
| DiffDate |
|---|
| 61 |
如果將日期前後相反過來，會得到負數：
SELECT DATEDIFF(day, '2008-08-10', '2008-06-10') AS DiffDate
結果：
| DiffDate |
|---|
| -61 |
取得兩個日期之間差幾秒：
SELECT DATEDIFF(second, '2005-07-20', '2005-7-25 22:56:32') AS DiffDate
| DiffDate |
|---|
| 514592 |
取得一年中的第一天：
SELECT DATEADD(year, DATEDIFF(year, 0, GETDATE()), 0)
這個 SQL 的技巧是先取得和 1900-01-01 - 也就是上面的 0 的意思，SQL Server 中的第零天 - 差幾年 (從 DATEDIFF 得到一個整數)，再用 DATEADD 加 0 來將整數再轉成日期的型態。
取得一季中的第一天：
SELECT DATEADD(quarter, DATEDIFF(quarter, 0, GETDATE()), 0)
取得一個月中的第一天：
SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
取得一周中的第一天：
SELECT DATEADD(week, DATEDIFF(week, 0, GETDATE()), 0)
取得一個月中的最後一天：
SELECT DATEADD(millisecond, -3, DATEADD(month, DATEDIFF(month, 0, GETDATE()) + 1, 0))
-3 是因為 SQL Server datetime 的精度 (precision) 是到 3 毫秒。
SQL Server CONVERT() 格式化顯示日期和時間 (convert dateime)
在 SQL Server 中，可以用 CONVERT() 函數來用不同的格式顯示日期和時間。CONVERT() 語法 (Syntax)
CONVERT(data_type, date, style)
其中參數 data_type 可以指定輸出的資料型態，例如 varchar；也可以指定長度，例如 varchar(10)。
而參數 style 是用來指定日期和時間的輸出格式，可以是下列這些值：
| style id 不含世紀 (yy) | style id 含世紀 (yyyy) | 說明 | 顯示格式 |
|---|
- | 0
100 | datetime 和 smalldatetime 的預設值 | mon dd yyyy hh:miAM (或 PM) 1 | 101 | 美式英文 | 1 = mm/dd/yy
101 = mm/dd/yyyy 2 | 102 | ANSI | 2 = yy.mm.dd
102 = yyyy.mm.dd 3 | 103 | 英式英文/法文 | 3 = dd/mm/yy
103 = dd/mm/yyyy 4 | 104 | 德文 | 4 = dd.mm.yy
104 = dd.mm.yyyy 5 | 105 | 義大利文 | 5 = dd-mm-yy
105 = dd-mm-yyyy 6 | 106 | - | 6 = dd mon yy
106 = dd mon yyyy 7 | 107 | - | 7 = Mon dd, yy
107 = Mon dd, yyyy 8 | 108 | - | hh:mi:ss - | 9
109 | 預設值 + 毫秒 | mon dd yyyy hh:mi:ss:mmmAM (或 PM) 10 | 110 | USA | 10 = mm-dd-yy
110 = mm-dd-yyyy 11 | 111 | 日本 | 11 = yy/mm/dd
111 = yyyy/mm/dd 12 | 112 | ISO | 12 = yymmdd
112 = yyyymmdd - | 13
113 | 歐洲預設值 + 毫秒 | dd mon yyyy hh:mi:ss:mmm(24h) 14 | 114 | - | hh:mi:ss:mmm(24h) - | 20
120 | ODBC 標準 | yyyy-mm-dd hh:mi:ss(24h) - | 21
121 | time、date、datetime2 和 datetimeoffset 的 ODBC 標準 (使用毫秒) 預設值 | yyyy-mm-dd hh:mi:ss.mmm(24h) - | 126 | ISO8601 | yyyy-mm-ddThh:mi:ss.mmm (無空格)
注意：針對毫秒 (mmm) 值 0，不會顯示毫秒十進位小數值。 例如，'2012-11-07T18:26:20.000' 值會顯示為 '2012-11-07T18:26:20'。 - | 127 | 具有時區 Z 的 ISO8601 | yyyy-mm-ddThh:mi:ss.mmmZ (無空格)
注意：針對毫秒 (mmm) 值 0，不會顯示毫秒十進位值。 例如，'2012-11-07T18:26:20.000' 值會顯示為 '2012-11-07T18:26:20'。 - | 130 | 回曆 | dd mon yyyy hh:mi:ss:mmmAM
在此樣式中，mon 代表完整月份名稱的多 Token 回曆 Unicode 表示法。 這個值無法在 SSMS 的預設美國安裝中正確呈現。 - | 131 | 回曆 | dd/mm/yyyy hh:mi:ss:mmmAM
CONVERT() 用法 (Example)
SELECT CONVERT(varchar, GETDATE(), 10) AS Converted
| Converted |
|---|
| 12-20-18 |
SELECT CONVERT(varchar, GETDATE(), 100) AS Converted
| Converted |
|---|
| Dec 20 2018 1:18PM |
留言
張貼留言