DIFF BETWEEN DATETIME AND DATETIMEOFFSET DATATYPES IN SQL SERVER :INTERVIEW QUESTION

Difference between DateTime and DateTimeOffset Data types in SQL Server : Interview Questions
Following are the difference between these two datatypes

DateTimeDateTimeOffset[(n)]
1 - Min Value1753-01-01 00:00:000001-01-01 00:00:00 +05:30
This datatype also display time zone.

2 - Max Value9999-12-31 23:59:59.9979999-12-31 23:59:59.9999999 +05:30
3 - Time zone offset rangeTime zone not supported-14:00 through +14:00
4 - Size8 bytes is required to store the valueDepends on the millisecond precision; 8 to 10 bytes are required to store the value
5 - SyntaxSyntax is simply DATETIME
DECLARE @CurrentDate DATETIME
Syntax is DATETIMEOFFSET [ (Fractional Seconds Precision) ],
with a default value of 7 for the fractional seconds precision.
DECLARE @CurrentDateTime DATETIMEOFFSET(5)
6 - AccuracyAccuracy is up to 0.00333 secondAccuracy is up to 100 nanoseconds
7 - Time rangeTime range is between 00:00:00 through 23:59:59.997Time range is between 00:00:00 through 23:59:59.9999999
8- Current Date and Time functionSELECT GETDATE()
2015-08-26 22:56:34.670
SELECT SYSDATETIMEOFFSET()
2015-08-27 00:11:04.3897800 +05:30
9 - Addition or subtractionAddition 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,SYSDATETIMEOFFSET())

But SELECT SYSDATETIMEOFFSET() +1 will throw error
10 - PrecisionNo precision or scale can be specifiedPrecision or scale is from 0 to 7 digits.
Ex: DATETIMEOFFSET(5)
11 - AvailablityAvailable from SQL Server 2000 and onwardsAvailable only from SQL Server 2008 and onwards

Comments

Contact Form

Send