https://docs.microsoft.com/en-us/sql/t-sql/data-types/date-and-time-types
date (Transact-SQL)
datetime (Transact-SQL)
datetime2 (Transact-SQL)
datetimeoffset (Transact-SQL)
smalldatetime (Transact-SQL)
time (Transact-SQL)
https://stackoverflow.com/questions/1334143/datetime2-vs-datetime-in-sql-server
CREATE TABLE TestTime ( TempTime TIME , TempDate DATE , TempSMALLDATETIME SMALLDATETIME , TempDateTime DATETIME , TempDateTime2 DATETIME2 , TempDATETIMEOFFSET DATETIMEOFFSET ); SELECT * FROM dbo.TestTime; DECLARE @TempTime TIME; DECLARE @TempDate DATE; DECLARE @TempSMALLDATETIME SMALLDATETIME; DECLARE @TempDateTime DATETIME; DECLARE @TempDateTime2 DATETIME2; DECLARE @TempDATETIMEOFFSET DATETIMEOFFSET; SELECT @TempTime; SELECT @TempDate; SELECT @TempSMALLDATETIME; SELECT @TempDateTime; SELECT @TempDateTime2; SELECT @TempDATETIMEOFFSET;
TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )
fractions表示不到一秒的时间的数值。【比如1毫秒,10毫秒】
precision决定了fractions的时间单位。因为Time的精度是100纳秒,所以precision的取值范围是[1,7]。
然后fractions和precision组合起来,是不允许超过1秒的。
如果precision为3,那么fraction的单位是10^-3秒,即1毫秒。
如果precision为6,那么fraction的单位是10^-6秒,即1微秒。
如果precision为9,那么fraction的单位是10^-9秒,即1纳秒。
如果precision为2,那么fraction的单位是10^-2秒,即10毫秒。
SELECT TIMEFROMPARTS(23, 59, 59, POWER(10, 7)-1, 7) --23:59:59.9999999
DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )
DateTime的精度是0.00333秒,即3.33毫秒
SELECT DATETIMEFROMPARTS(2017, 12, 31, 23, 59, 59, 991) ,--2017-12-31 23:59:59.990 DATETIMEFROMPARTS(2017, 12, 31, 23, 59, 59, 992) ,--2017-12-31 23:59:59.993 DATETIMEFROMPARTS(2017, 12, 31, 23, 59, 59, 993) ,--2017-12-31 23:59:59.993 DATETIMEFROMPARTS(2017, 12, 31, 23, 59, 59, 994) ,--2017-12-31 23:59:59.993 DATETIMEFROMPARTS(2017, 12, 31, 23, 59, 59, 995) ,--2017-12-31 23:59:59.997 DATETIMEFROMPARTS(2017, 12, 31, 23, 59, 59, 996) ,--2017-12-31 23:59:59.997 DATETIMEFROMPARTS(2017, 12, 31, 23, 59, 59, 997) ,--2017-12-31 23:59:59.997 DATETIMEFROMPARTS(2017, 12, 31, 23, 59, 59, 998) ,--2017-12-31 23:59:59.997 DATETIMEFROMPARTS(2017, 12, 31, 23, 59, 59, 999);,--2018-01-01 00:00:00.000