difference between datetime and datetime2 sql server

difference between datetime & datetime2 in SQL Server
We all aware about the common datatype in SQL Server. In SQL Server each column,varibale & parameter has a different different kind of datatype, Basically datatype is a property that speicify the what kind of data that object hold.Such as int,varchar,text,datetime etc.We discuss here sql server datetime datatype. In SQL Server 2008 there are more data type added and datetime2 is one of them. We discuss here two datetime related datatype here. Lower version of SQL Server only have one datatype to store datetime and this is simply datetime2. Now in SQL server 2008 DBA have a option to choose one of them. First is datetime & another is datetime2. There are lots of similarity & difference between these two. Similarity are given below
 1. Both SQL Server datatype is use for define the date and time in SQL server Column.
 2. These datatype is not aware about the daylight saving time.
 3. Max limit of both datatype is same & its '9999-12-31 23:59:59'.
 4. Both are the most common use ms sql data types.

Difference between datetime & datetime2 in SQL Server

datetime datetime2
Datetime always occupies 8 byte to store the date. Datetime2 storage is depends on the what you store & its storage size vary from 6 to 8 byte.
Not ANSI and ISO compliant. DateTime2 is ANSI and ISO 8601 compliant. 
DateTime does not have any such provision. DateTime2 give you configure precision of time part while declaring the fields.
DATETIME DATETIME2(n). Where n is optional and its range is 0 to 7. If n is not specified then fractional seconds precision is 7 digit by default.
datetime  function example
GETDATE() we all aware about the getdate() function. it return the current date and time & return data type is datetime SYSDATETIME() this function also return the system datetime but the return value data type is datetime2
SQL Server Version
Available from SQL Server 2000 and onwards Available from SQL Server 2008 and onwards
Accuracy is up to 0.00333 second Accuracy is up to 100 nanoseconds
Add day is possible (+/-)
Yes. For Example  Select getdate()+1 working fine with datetime datatype.No. You have to use dateadd function to add day or month but if you used getdate()+1 then it throw the error . Error is Msg 206, Level 16, State 2, Line 2
Operand type clash: datetime2 is incompatible with int


Post a Comment