SQL SERVER INTERVIEW QUESTIONS : DIFF BETWEEN DATETIME AND DATETIME2
Difference between DateTime and DateTime2 Data types in SQL Server : Interview Questions Following are the difference between these two da...
Following are the difference between these two datatypes
|1 - Min Value||1753-01-01 00:00:00||0001-01-01 00:00:00|
|2 - Max Value||9999-12-31 23:59:59.997||9999-12-31 23:59:59.9999999|
|3 - Size||8 bytes is required to store the value||Depends on the millisecond precision; 6 to 8 bytes are required to store the value|
|4 - Syntax||Syntax is simply DATETIME
DECLARE @CurrentDate DATETIME
|Syntax is DATETIME2 [ (Fractional Seconds Precision) ],
with a default value of 7 for the fractional seconds precision.
DECLARE @CurrentDateTime DATETIME2(7)
|5 - Accuracy||Accuracy is up to 0.00333 second||Accuracy is up to 100 nanoseconds|
|6 - Time range||Time range is between 00:00:00 through 23:59:59.997||Time range is between 00:00:00 through 23:59:59.9999999|
|7- Current Date and Time function||SELECT GETDATE()
|8 - Addition or subtraction||Addition or subtraction to numbers is directly allowed.
SELECT GETDATE() + 1
|Addition or subtraction to numbers is not directly allowed. The function DATEADD
should be used
Ex SELECT DATEADD(DAY,1,SYSDATETIME())
But SELECT SYSDATETIME() +1 will throw error
|9 - Precision||No precision or scale can be specified||Precision or scale is from 0 to 7 digits.
|10 - Availablity||Available from SQL Server 2000 and onwards||Available only from SQL Server 2008 and onwards|