DateTime2 is the new Data Type introduced in Sql Server 2008 for storing Date and Time value. As per MSDN, Microsoft Suggests to use this new Data Type for new work instead of DateTime.
Following table summarizes some of the major difference between this new DateTime2 and the old DateTime Data Type.
| DateTime | DateTime2[(n)] | |
| Min Value | 1753-01-01 00:00:00 | 0001-01-01 00:00:00 |
| Max Value | 9999-12-31 23:59:59.997 | 9999-12-31 23:59:59.9999999 |
| Storage Size | 8 Bytes | 6 to 8 bytes Note: Parameter n is optional and if it is not specified then fractional seconds precision is 7 digit and it can be from 0 to 7 digit. For fractional seconds precision <3, takes 6 bytes For fractional seconds precision 3 or 4 it will take 7 bytes For fractional seconds precision >4 it will take 8 bytes |
| Usage | Declare @now datetime | Declare @now datetime2(7) |
| Compliance | Is not an ANSI/ISO compliant | Is an ANSI/ISO compliant |
| Current Date and Time function | GetDate() – It returns DB Current Date and Time of DateTime Data Type
Example: SELECT GETDATE() |
SYSDATETIME()– It returns DB Current Date and Time of DateTime2 Data Type
Example:SELECT SYSDATETIME() |
| +/- days | WORKS
Example: |
FAILS – Need to use only DateAdd function
Example: |
DateTime2 with fractional seconds precision of 3 is same as DateTime data type. And DateTime2(3) uses 7 bytes of storage instead of 8 byte which old DateTime datatype uses and it also provides higher date range (i.e. 0001-01-01 to 9999-12-31 ) compared to DateTime data type. Now let us see this with an example:
DECLARE @nowDateTime DATETIME = GETDATE(),
@nowDateTime2 DATETIME2(3)= SYSDATETIME()
SELECT DATALENGTH(@nowDateTime) 'DateTime Storage Size',
DATALENGTH(@nowDateTime2) 'DateTime2(3) Storage Size'
Result: DateTime Storage Size DateTime2 Storage Size --------------------- ---------------------- 8 7
ALSO READ
- Varchar vs NVarchar
- Varchar vs Varchar(MAX)
- Char vs Varchar
- Text vs Varchar(Max)
- Union vs Union All
- DateTime vs DateTime2
- SET QUOTED_IDENTIFIER ON vs SET QUOTED_IDENTIFIER OFF
- Stored Procedure vs User Defined Function
- Primary Key vs Unique Key
- RAISERROR vs THROW
- Temporary Table vs Table Variable
- Len() vs Datalength()
- Sequence vs Identity
- DATEDIFF vs DATEDIFF_BIG
- LEFT JOIN vs LEFT OUTER JOIN
- RIGHT JOIN vs RIGHT OUTER JOIN
- JOIN vs INNER JOIN
- LEFT OUTER JOIN vs RIGHT OUTER JOIN
- SMALLDATETIME vs DATETIME
Good work Basawaraj. Nice way 2 serve techincal community by spreading your experience and knowledge.
Thank you Pinna
Good article showing the differences between DateTime and DateTime2. Precise and Easy.
very good article.. well designed and to the point. Slow students like me requires this type of tutorial. Where things are broken into pieces and effect is shown by example. thanks mate.
A fascinating discussion is worth comment. I believe that you need
to publish more on this issue, it may not be a taboo subject but generally people do not speak about such topics.
To the next! All the best!!
Thanks!
Also, for “DateTime2” (vs. “DateTime”):
1. Using “DateAdd” Function is not a trivial workaround for not being not having implicit / easy conversion to a floating-point numeric (# of days.fraction of a day) since min date-time, esp. you’re trying to take into account multiple (if not all) parts of the date (i.e. adding / subtracting 2 full date-times together / from each other).
2. Can’t easily calculate “age” (e.g. by just subtracting one “DateTime” from another vs. using “DateDiff” Function which doesn’t return “age” when the two date-times happen to be on opposite sides of a calendar / clock boundary of the unit specified).
3. Can’t easily use in the “Avg” Aggregate Function (by just “Cast”‘ing to “Float” first and back again to “DateTime”). Btw, if you’re asking what the purpose is of getting an “Avg” of date-times: besides use in getting average duration when date-times (since a common base date-time) are used to represent duration (a common practice), it’s also useful to get for dashboard-type statistic on what the average date-time is in a range / group of date-times. A standard ad-hoc query to research / troubleshoot values in a Column that may not be valid ever / any longer and / or may need to be deprecated is to list for each value the occurrence count and (if available) the min, avg and max date-time stamps associated with that value.
Yes, I realize there is a (fairly complex) workaround formula (at “https://siderite.blogspot.com/2015/08/how-to-translate-t-sql-datetime2-to.html”), but even that formula only works for date-times newer than year 1970 (which means your losing all the extra range *plus* 217 years and that extra range is probably (supposedly) one of the 2 biggest pros of “DateTime2” albeit probably not needed in most use cases), and it’s not necessarily trivial to make it support more range due to possible numeric overflow issues.
And, yes, I realize you could also “Cast” to “DateTime” first (and if necessary back again to “DateTime2”), but you’d lose the extra precision and again, the extra range (all prior to year 1753) benefits of “DateTime2” vs. “DateTime” which again are probably the 2 biggest and also at the same time, probably not likely needed, which begs the question why use it when you lose the implicit / easy conversions to floating-point numeric (# of days) for addition / subtraction / “age” (vs. DateDiff) / Avg calcs benefit which is a big one in my experience.
Nicely explained.
can you please post blog/article/solution for removing duplicate records from table without adding Identity column & table having records in billions.
Thanks
Jagannath
datetime2(3) is NOT the same as datetime. They will have the same number of digits, but the precision of datetime is 3.33ms, while the precision of datetime2(3) is 1ms.
DECLARE @nowDateTime2 DATETIME2=SYSDATETIME()
select @nowDateTime2,
cast(@nowDateTime2 as datetime2(3)),
cast(@nowDateTime2 as datetime)
It will return the following three values:
2014-09-10 12:15:30.5136440
2014-09-10 12:15:30.514 — datetime2(3) uses typical rounding to whole millisecond
2014-09-10 12:15:30.513 — datetime will round to 0, 3 or 7 milliseconds.
thanks mr
very thanks