What
are the differences between DATETIME and DATETIME2(N) [DATETIME VS DATETIME2(N)]?
Which
data type should use for defining a column in a table [DATETIME VS
DATETIME2]?
DATETIME
|
DATETIME2(N)
|
|
Bytes
|
It consumes 8 bytes.
|
It consumes 6 to 8 bytes based on the optional parameter (N)
for fractional seconds precision storage.
|
Minimum Value
|
1753-01-01
00:00:00.000
YYYY-mm-DD HH:MM:SS.ms
Gives 3 digits of milliseconds.
|
0001-01-01
00:00:00.0000000
YYYY-mm-DD HH:MM:SS.ms
Gives 7 digits of
milliseconds.
|
Maximum Value
|
9999-12-31 23:59:59.997.
|
9999-12-31
23:59:59.9999999.
|
Data
Storage
Type
|
Used to store the date
time data with maximum 3 digits fractional seconds precision.
|
Used to store date
time data with maximum 7 digits fractional seconds precision.
|
Storage
|
Storage size is fixed 8 bytes.
|
Storage size is
varying based on the optional parameter. (N)
N – Specifies the
number of digit fractional seconds precision would be stored.
N=0 or then consumes 8 bytes
N<3 then consumes 6
bytes
N>=3 and <=4
then consumes 7 bytes
N>4 then consumes 8
bytes
|
Use When?
|
3 digits fractional
seconds precision will full fill your requirements.
SELECT GETDATE()
OUTPUT:
2016-11-24 17:25:22.040
|
More than 3 digits fractional
seconds precision will full fill your requirements.
SELECT SYSDATETIME()
OUTPUT:
2016-11-24
17:26:45.5312071
|
Optional Parameter
value
|
No optional parameter for this data type.
DECLARE @Now DATETIME = GETDATE()
PRINT @Now
OUTPUT:
Nov 24 2016 5:30PM
|
The optional parameter value N [DATETIME2(N)] is not
specified in the variable declaration or column definition then it is
considered as 7.
DECLARE @Now DATETIME2 = SYSDATETIME()
PRINT @Now
OUTPUT:
2016-11-24
17:26:45.5312071
DECLARE @Now DATETIME2(3) = SYSDATETIME()
PRINT @Now
OUTPUT:
2016-11-24
17:26:45.531
|
+ OR - Days
|
It supports + or – for adding to or subtracting from
and also use DATEADD function to add or subtract.
DECLARE @Now DATETIME = DATEADD(D, -96000, GETDATE())
PRINT @Now
OUTPUT:
Jan 22 1754 5:43PM
DECLARE @Now DATETIME = GETDATE() - 96000
PRINT @Now
OUTPUT:
Jan 22 1754 5:43PM
|
It does not support + or – for adding days to or subtracting days from date.
DECLARE @Now DATETIME2(2)
SET @Now = SYSDATETIME()- 96000
PRINT @Now
Error:
Msg
206, Level 16, State 2, Line 2
Operand
type clash: datetime2 is incompatible with int
DECLARE @Now DATETIME2(7) = DATEADD(D, -700000, SYSDATETIME())
PRINT @Now
OUTPUT:
0100-05-13 17:46:17.8562603
|
Data Conversion
|
DECLARE @Now DATETIME = GETDATE()
PRINT CONVERT(VARCHAR(20), @Now, 101)
PRINT CONVERT(VARCHAR(20), @Now, 102)
PRINT CONVERT(VARCHAR(20), @Now, 103)
PRINT CONVERT(VARCHAR(20), @Now, 104)
PRINT CONVERT(VARCHAR(20), @Now, 105)
PRINT CONVERT(VARCHAR(20), @Now, 106)
PRINT CONVERT(VARCHAR(20), @Now, 107)
PRINT CONVERT(VARCHAR(20), @Now, 108)
OUTPUT:
11/24/2016
2016.11.24
24/11/2016
24.11.2016
24-11-2016
24 Nov
2016
Nov 24,
2016
17:35:34
|
DECLARE @Now DATETIME2(7) = SYSDATETIME()
PRINT CONVERT(VARCHAR(20), @Now, 101)
PRINT CONVERT(VARCHAR(20), @Now, 102)
PRINT CONVERT(VARCHAR(20), @Now, 103)
PRINT CONVERT(VARCHAR(20), @Now, 104)
PRINT CONVERT(VARCHAR(20), @Now, 105)
PRINT CONVERT(VARCHAR(20), @Now, 106)
PRINT CONVERT(VARCHAR(20), @Now, 107)
PRINT CONVERT(VARCHAR(20), @Now, 108)
PRINT CONVERT(VARCHAR(20), @Now, 109)
PRINT CONVERT(VARCHAR(20), @Now, 110)
… 115
OUTPUT:
11/24/2016
2016.11.24
24/11/2016
24.11.2016
24-11-2016
24 Nov
2016
Nov 24,
2016
17:56:57
Nov 24
2016 5:56:57
11-24-2016
|
No comments:
Post a Comment