Data types are important to understand as they can save space on your hard drive, memory, and network. When working with time precision and the Date and Time data types we can conserve space by accurately using the TIME data type.

DECLARE @Time1 DATETIME = '15:30:45'
DECLARE @Time2 DATETIME2 = '15:30:45'
DECLARE @Time3 TIME = '15:30:45'
DECLARE @Time4 TIME(0) = '15:30:45'
DECLARE @Time7 TIME(7) = '15:30:45'

SELECT @Time1 as DT_Example, 
       @Time2 as DT_Example2,
       @Time3 as TimeOnly,
       @Time4 as TimeZero,
       @Time7 as TimeSeven

In the code above, I have declared five variables of different date and time data types. I then selected each variable to see the result. Let’s look at the results of the DateTime and DateTime2 data types first.

We can notice that even though we didn’t specify the date in our variables for either the DateTime or DateTime2 data types, the date of January 1st, 1900 was included. This the default value of these data types. If we had only included a date instead of time, the time value would have defaulted to midnight. If we only need a date value we should use the Date data type and if we only need a time value we should use the Time data type.

Finally, with this example, notice the only difference between the two data types is their time accuracy. DateTime is accurate out to 3.33 milliseconds while DateTime2 is accurate out to 100 nanoseconds.

Next, let’s focus on the three variables that only used the TIME data type. When we declared the first variable, we did not provide parentheses for a precision value, this provides the default setting. The second variable we included a precision value of 0 in the parentheses and the final variable we used a 7 in the parentheses to specify how precise we wanted the results.

Notice that there is no difference between the first variable and the third variable. This is because the TIME data type will be accurate out to 100 nanoseconds unless we specify with a value less than 7. In the case of the second variable, we used the value of 0 to have our time to be precise only to the second.