Datetime Vs Datetime2 datatypes

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

Advertisement

Question of the month October 2015 – Find out the problamatic statement

Out of the following three statements, find out the one that throws an error

–statement 1

select count(*)/count(1)+null as result

–statement 2

select count(*)/count(1)/null as result

–statement 3

select count(*)/count(1+null) as result


Computed parameters in Stored Procedure

You know that you can create computed column in a table using other columns of the same table. Did you know that you can create a parameter in a stored procedure similar to that

Consider the following Stored Procedure

use tempdb
GO
Create procedure paramter_testing
(
@date_and_time datetime,
@date_only date = @date_and_time
)
as
select @date_and_time as date_and_time , @date_only as date_only

If you execute the above procedure by

EXEC parameter_testing '20101015 14:12:10'

THE result is

date_and_time               date_only
--------------------------   ------------
2010-10-15 14:12:10.000    2010-10-15

As you see the parameter value for @date_only is computed based on the other parameter @date_with_time and time part is truncated as it is of DATE datatype

Note : It is not possible to apply functions on the parameter at the time of declaration (ex @date_only Date=dateadd(month,1,@date_with_time), etc)