[MS-SQL] date, datetime, datetime2, datetimeoffset, smalldatetime, time 의 차이?

date, datetime, datetime2, datetimeoffset, smalldatetime, time 의 차이

백번 듣는것 보다 바로 쿼리 돌려서 차이점을 비교하고 이해하는 것이 빠르다.

-DATE

SELECT GETDATE() AS CurrentDateTime,
       CAST(GETDATE() AS DATE) AS DateOnly

--CurrentDateTime         DateOnly
------------------------- ----------
--2023-06-14 10:27:01.420 2023-06-14

-DATETIME

SELECT GETDATE() AS CurrentDateTime,
       CAST(GETDATE() AS DATETIME) AS DateTimeValue

--CurrentDateTime         DateTimeValue
------------------------- -----------------------
--2023-06-14 10:27:01.420 2023-06-14 10:27:01.420

-DATETIME2

SELECT SYSDATETIME() AS CurrentDateTime,
       CAST(SYSDATETIME() AS DATETIME2) AS DateTime2Value

--CurrentDateTime             DateTime2Value
----------------------------- ---------------------------
--2023-06-14 10:27:01.4239671 2023-06-14 10:27:01.4239671

-DATETIMEOFFSET

SELECT SYSDATETIMEOFFSET() AS CurrentDateTimeOffset

--CurrentDateTimeOffset
------------------------------------
--2023-06-14 10:27:01.4239671 +09:00

-SMALLDATETIME

SELECT GETDATE() AS CurrentDateTime,
       CAST(GETDATE() AS SMALLDATETIME) AS SmallDateTimeValue

--CurrentDateTime         SmallDateTimeValue
------------------------- -----------------------
--2023-06-14 10:27:01.420 2023-06-14 10:27:00

-TIME

SELECT SYSDATETIME() AS CurrentDateTime,
       CAST(SYSDATETIME() AS TIME) AS TimeOnly

--CurrentDateTime             TimeOnly
----------------------------- ----------------
--2023-06-14 10:27:01.4239671 10:27:01.4239671