DateTime2 vs DateTime in SQL Server

 

Which one:

  • datetime
  • datetime2

    • is the recommended way to store date and time in SQL Server 2008+?

    I'm aware of differences in precision (and storage space probably), but ignoring those for now, is there a best practice document on when to use what, or maybe we should just use datetime2 only?

    The MSDN documentation for datetime recommends using datetime2. Here is their recommendation:

    Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides time zone support for globally deployed applications.

    datetime2 has larger date range, a larger default fractional precision, and optional user-specified precision. Also depending on the user-specified precision it may use less storage.

    DATETIME2 has a date range of "0001 / 01 / 01" through "9999 / 12 / 31" while the DATETIME type only supports year 1753-9999.

    Also, if you need to, DATETIME2 can be more precise in terms of time; DATETIME is limited to 3 1/3 milliseconds, while DATETIME2 can be accurate down to 100ns.

    Both types map to System.DateTime in .NET - no difference there.

    If you have the choice, I would recommend using DATETIME2 whenever possible. I don't see any benefits using DATETIME (except for backward compatibility) - you'll have less trouble (with dates being out of range and hassle like that).

    Plus: if you only need the date (without time part), use DATE - it's just as good as DATETIME2 and saves you space, too! :-) Same goes for time only - use TIME . That's what these types are there for!

    datetime2 wins in most aspects except (old apps Compatibility)

  • larger range of values
  • better Accuracy
  • smaller storage space (if optional user-specified precision is specified)

    • SQL日期和时间数据类型compare - datetime，datetime2，date，TIME

    please note the following points

  • Syntax
  • datetime2[(fractional seconds precision=> Look Below Storage Size)]
  • Precision, scale
  • 0 to 7 digits, with an accuracy of 100ns.
  • The default precision is 7 digits.
  • Storage Size
  • 6 bytes for precision less than 3;
  • 7 bytes for precision 3 and 4.
  • All other precision require 8 bytes .
  • DateTime2(3) have the same number of digits as DateTime but uses 7 bytes of storage instead of 8 byte (SQLHINTS- DateTime Vs DateTime2)
  • Find more on datetime2(Transact-SQL MSDN article)

    • image source : MCTS Self-Paced Training Kit (Exam 70-432): Microsoft® SQL Server® 2008 - Implementation and Maintenance Chapter 3:Tables -> Lesson 1: Creating Tables -> page 66

    链接地址: http://www.djcxy.com/p/16892.html

    上一篇: 如何列出在SQL Server中引用给定表的所有外键？

    下一篇: DateTime2与SQL Server中的DateTime