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 datatypes

DateTime DateTime2[(n)]
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()
2015-08-26 22:56:34.670
SELECT SYSDATETIME()
2015-08-26 22:56:34.6736314
8 - Addition or subtraction Addition or subtraction to numbers is directly allowed.
Ex
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.
Ex: Datetime2(6)
10 - Availablity Available from SQL Server 2000 and onwards Available only from SQL Server 2008 and onwards

Comments

Archive

Contact Form

Send