One of my friends asked me if there is any difference between Datetime and Datetime2 datatypes other than the difference in the millisecond fractional values (3 digits for Datetime and 7 digits for Datetime2).
Yes there are other differences too.
Datetime:
Minimum value is 1753-01-01
It requires 8 bytes to store values
The last digit of the millisecond is always 0,3 or 7
Millisecond can be expressed using either colon or a dot
declare @datetime datetime
select @datetime='2015-10-10 14:22:34.168'
select @datetime
GO
declare @datetime datetime
select @datetime=‘2015-10-10 14:22:34:168’
select @datetime
GO
The result is 2015-10-10 14:22:34.167
Datetime2
Minimum value is 0001-01-01
It requires 6 to 8 bytes depends on number of milliseconds to store values
Millisecond can be expressed using only dot but using a colon will throw an error
declare @datetime datetime2(7)
select @datetime='2015-10-10 14:22:34.1682345'
select @datetime
The result is 2015-10-10 14:22:34.1682345
If you use a colon
declare @datetime datetime2(7)
select @datetime='2015-10-10 14:22:34:1682345'
select @datetime
you get an error
Msg 241, Level 16, State 1, Line 4
Conversion failed when converting date and/or time from character string.
You may need to read this post on Millisecond values and separator in time values