DateTime2 is the new Data Type introduced in Sql Server 2008 for storing Date and Time value. As per MSDN, Microsoft Suggests to use this new Data Type for new work instead of DateTime.
Following table summarizes some of the major difference between this new DateTime2 and the old DateTime Data Type.
| DateTime | DateTime2[(n)] | |
| Min Value | 1753-01-01 00:00:00 | 0001-01-01 00:00:00 |
| Max Value | 9999-12-31 23:59:59.997 | 9999-12-31 23:59:59.9999999 |
| Storage Size | 8 Bytes | 6 to 8 bytes Note: Parameter n is optional and if it is not specified then fractional seconds precision is 7 digit and it can be from 0 to 7 digit. For fractional seconds precision <3, takes 6 bytes For fractional seconds precision 3 or 4 it will take 7 bytes For fractional seconds precision >4 it will take 8 bytes |
| Usage | Declare @now datetime | Declare @now datetime2(7) |
| Compliance | Is not an ANSI/ISO compliant | Is an ANSI/ISO compliant |
| Current Date and Time function | GetDate() – It returns DB Current Date and Time of DateTime Data Type
Example: SELECT GETDATE() |
SYSDATETIME()– It returns DB Current Date and Time of DateTime2 Data Type
Example:SELECT SYSDATETIME() |
| +/- days | WORKS
Example: |
FAILS – Need to use only DateAdd function
Example: |
DateTime2 with fractional seconds precision of 3 is same as DateTime data type. And DateTime2(3) uses 7 bytes of storage instead of 8 byte which old DateTime datatype uses and it also provides higher date range (i.e. 0001-01-01 to 9999-12-31 ) compared to DateTime data type. Now let us see this with an example:
DECLARE @nowDateTime DATETIME = GETDATE(),
@nowDateTime2 DATETIME2(3)= SYSDATETIME()
SELECT DATALENGTH(@nowDateTime) 'DateTime Storage Size',
DATALENGTH(@nowDateTime2) 'DateTime2(3) Storage Size'
Result: DateTime Storage Size DateTime2 Storage Size --------------------- ---------------------- 8 7
ALSO READ
- Varchar vs NVarchar
- Varchar vs Varchar(MAX)
- Char vs Varchar
- Text vs Varchar(Max)
- Union vs Union All
- DateTime vs DateTime2
- SET QUOTED_IDENTIFIER ON vs SET QUOTED_IDENTIFIER OFF
- Stored Procedure vs User Defined Function
- Primary Key vs Unique Key
- RAISERROR vs THROW
- Temporary Table vs Table Variable
- Len() vs Datalength()
- Sequence vs Identity
- DATEDIFF vs DATEDIFF_BIG
- LEFT JOIN vs LEFT OUTER JOIN
- RIGHT JOIN vs RIGHT OUTER JOIN
- JOIN vs INNER JOIN
- LEFT OUTER JOIN vs RIGHT OUTER JOIN
- SMALLDATETIME vs DATETIME