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
Following are the difference between these two datatypes
| DateTime | DateTimeOffset[(n)] | |
| 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() 2015-08-26 22:56:34.670 |
SELECT SYSDATETIMEOFFSET()
2015-08-27 00:11:04.3897800 +05:30 |
| 9 - 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,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. Ex: DATETIMEOFFSET(5) |
| 11 - Availablity | Available from SQL Server 2000 and onwards | Available only from SQL Server 2008 and onwards |
Comments