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 thes...
Following are the difference between these two datatypes
|1 - Min Value||1753-01-01 00:00:00||0001-01-01 00:00:00 +05:30|
This datatype also display time zone.
|2 - Max Value||9999-12-31 23:59:59.997||9999-12-31 23:59:59.9999999 +05:30|
|3 - Time zone offset range||Time zone not supported||-14:00 through +14:00|
|4 - Size||8 bytes is required to store the value||Depends on the millisecond precision; 8 to 10 bytes are required to store the value|
|5 - Syntax||Syntax 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 - Accuracy||Accuracy is up to 0.00333 second||Accuracy is up to 100 nanoseconds|
|7 - 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|
|8- Current Date and Time function||SELECT GETDATE()|
SELECT SYSDATETIMEOFFSET()2015-08-27 00:11:04.3897800 +05:30
|9 - 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,SYSDATETIMEOFFSET())
But SELECT SYSDATETIMEOFFSET() +1 will throw error
|10 - Precision||No precision or scale can be specified||Precision or scale is from 0 to 7 digits.|
|11 - Availablity||Available from SQL Server 2000 and onwards||Available only from SQL Server 2008 and onwards|