目錄
志銘-2021年10月31日 22:57:15
0. 日期和時間型別
0.0 時間型別
| 資料型別 | 時間範圍 | 準確度 | 推薦格式 | 示例 |
|---|---|---|---|---|
| DATETIME | 1753-01-01到9999-12-31 | 3.33毫秒 | 'YYYYMMDD hh:mm:ss.nnn' | 2021-10-23 14:35:20.727 |
| SMALLDATETIME | 1900-01-01到2079-06-06 | 1分鐘 | 'YYYYMMDD hh:mm' | 2021-10-23 14:41:00 |
| DATE | 0001-01-01到9999-12-31 | 1天 | 'YYYY-MM-DD' | 2021-10-23 |
| TIME | 00:00:00:0000000到23:59:59.9999999 | 100納秒 | 'hh:mm:ss:nnnnnnn' | 14:44:35.4170000 |
| DATETIME2 | 0001-01-01 00:00:00:0000000到9999-12-31 23:59:59.9999999 | 100納秒 | 'YYYY-MM-DD hh:mm:ss.nnnnnnn' | 2021-10-23 14:46:10.9600000 |
| DATETIMEOFFSET | 0001-01-01 00:00:00:0000000到9999-12-31 23:59:59.9999999 | 100納秒 | 'YYYY-MM-DD hh:mm:ss.nnnnnnn [+|-] hh:mm' | 2021-10-23 14:48:30.8730000 +00:00 |
- [注1] SQL Server2008之前的時間型別只有 DATETIME 和 SMALLDATETIME ，2008之後才出現日期和時間的單獨型別
- [注2] DATETIME2比DATETIME的範圍變大了，精確杜也提高了
- [注3] 推薦格式只是推薦格式，其上任何日期型別，都是即支援“YYYY-MM-DD”也是支援“YYYYMMDD”
- [注4] DATETIMEOFFSET中的 +/- hh:mm表示的時區差
- UTC時間：世界協調時間，當今世界的標準時間
- UTC+時區差=本地時間，時區：東為正，西為負
- 北京時區是東八區，領先UTC時間8個小時
簡單測試，有一個直觀的理解：
DECLARE @dateTime DATETIME = GETDATE();
SELECT @dateTime;
--結果：2021-10-23 11:53:56.420
DECLARE @smallDateTime SMALLDATETIME =GETDATE();
SELECT @smallDateTime
--結果：2021-10-23 11:54:00
DECLARE @date DATE=GETDATE();
SELECT @date
--結果：2021-10-23
DECLARE @time TIME =GETDATE();
SELECT @time
--結果：11:53:56.4200000
DECLARE @dateTime2 DATETIME2 =GETDATE();
SELECT @dateTime2
--結果：2021-10-23 11:53:56.4200000
DECLARE @dateTimeOffset DATETIMEOFFSET =GETDATE();
SELECT @dateTimeOffset
--結果：2021-10-23 11:53:56.4200000 +00:00
1. 轉換函式
1.1 CAST
-
【說明】 將指定的表示式轉換為目標資料型別。這裡有一個前提就是指定表示式理論上可以轉換為你指定的目標型別，負責報錯
-
【語法】
CAST( expressionString AS dataType) -
【示例】
SELECT CAST('012' AS INT)返回：12SELECT CAST('1.23' AS INT)返回：在將 varchar 值 '1.23' 轉換成資料型別 int 時失敗。
1.2 CONVERT
-
【說明】 其作用和CAST相同
- 但是CONVERT可以設定第三個引數來指定轉換的樣式，所以可以通過該引數將特定的時間字串轉為特定的時間格式
- CAST是標準SQL，而CONVERT不是標準SQL
-
【語法】
COVNERT(dataType,expressionString,[style_number]) -
【示例】
SELECT CONVERT(INT, '012')返回：12- TODO……
2. 日期操作函式
2.0 GETDATE和GETUTCDATE
-
【說明】 GETDATE和GETUTDATE兩個函式都是用於返回datetime型別的當前日期和時間
- GETUTDATE()是使用資料庫伺服器上的時區設定來求UTC時間
-
【示例】
SELECT GETDATE() --結果：2021-10-23 16:59:59.917 SELECT GETUTCDATE() --結果：2021-10-23 08:59:59.917 --可以直白的看到當前的北京時間（東八區）比世界協調時間快8個小時
2.1 SYSDATETIME和SYSUTCDATETIME
-
【說明】返回當前時間。等價於GERDATE和GETUTCDATE,這是在SQL Server2008中新增的，返回的2008中增加的DATETIME2型別的結果
-
【示例】
SELECT SYSDATETIME() --結果：2021-10-23 17:01:54.9879870 SELECT SYSUTCDATETIME() --結果：2021-10-23 09:01:54.9879870
2.2 DATEADD
-
【說明】 用於在日期值上加上指定單位指定的間隔
- 注意間隔值可以是負數，從而實現減去指定單位指定的間隔
-
【語法】
DATEADD(datePart,number,date)時間間隔 引數 年 Year，yyyy,yy 季度 Quarter,qq,q 月 Month,mm,m 一年內的天 DayOfYear,dy,y 天 Day,dd,d 星期 Week,wk,www -
【示例】
--當前日期加1天 SELECT DATEADD(Day,1,GETDATE()) --當前日期減1天 SELECT DATEADD(Day,-1,GETDATE())
2.3 DATEDIFF
-
【說明】 按照指定的間隔單位，計算兩個日期之間的間隔
-
【語法】
DATEDIFF(datePart,startDate,endDate)
注意：其中的datePart和DATEADD中的間隔單位是一樣的 -
【示例｝
--計算兩個日期之間間隔幾天 SELECT DATEDIFF(DAY,'2021-01-01','2021-01-02')--結果：1 --計算兩個日期之間間隔幾個月 SELECT DATEDIFF(MONTH,'2020-01-01','2021-01-02')--結果：12
2.4 DATEPART和DATENAME
-
【說明】獲取日期中的指定部分，如年，月，日
中文環境中二者不同之處:
DATEPART返回的是一個整形，
DATENAME返回的是一個字串型別（若是英文環境下，比如返回月份，則直接返回月份的名稱） -
【語法】
DATEPART(datePart,dateString)
DATENAME(datePart,dateString) -
【示例】
SELECT DATENAME(MONTH,'2021-01-01')--結果：1（字串型別） SELECT DATEPART(MONTH,'2021-01-01')--結果：1（整型）
2.5 YEAR、MONTH和DAY
-
【說明】 獲取指定日期中的中指定的部分，如 年，月，日
可以認為是DATEPART的簡化函式 -
【語法】
YEAR(dateString)
MONTH(dateString)
DAY(dateString) -
【示例】
SELECT YEAR('20211031')--結果：2021 SELECT MONTH('20211031')--結果：10 SELECT DAY('20211031')--結果：31
2.6 ISDATE
-
【說明】判斷指定的字串是否可以轉換為時間格式
-
【語法】
ISDATE(dateString) -
【示例】
SELECT ISDATE('20211031')--結果：1 SELECT ISDATE('20211031 22:46:01:01')--結果：1 SELECT ISDATE('2021-10-31')--結果：1 SELECT ISDATE('2021/10/31')--結果：1 SELECT ISDATE('2021-10-32')--結果：0
3. 綜合使用示例
3.1 根據出生日期計算年齡
CREATE TABLE #temptable
(
[Name] VARCHAR(4),
[Brithday] VARCHAR(10)
);
INSERT INTO #temptable
VALUES
('張三', '1994-01-01'),
('李四','1994-12-01');--注：測試日期是2021年10月31日
SELECT Name,
DATEDIFF(YEAR, Brithday, GETDATE()) AS Age,--精確到年
CONVERT(INT,DATEDIFF(DAY,Brithday,GETDATE())/365.25) AS Age--精確到日
FROM #temptable;
--結果：執行這段語句的日期是2021年10月31日，故李四還沒有到今年的生日，所以是26
--Name Age Age
------ -------- ---------
--張三 27 27
--李四 27 26