We all know that DATETIME and DATETIME2 both data types are available in Azure SQL Database or in general, SQL Server database. Let us see which one to go for as it was asked by a wife to her husband –
Wife: Honey, I am creating a new SQL table and need to store timestamp data in one of the columns. Which data type should I use – DATETIME or DATETIME2?
Husband: This should not be a question, in fact. Microsoft recommends to use DATETIME2 for any new work
Wife: Wow! Straight-forward and Understood. Does it mean that DATETIME has been deprecated?
Husband: No, Microsoft has not officially declared this as deprecated despite the recommendation to use DATETIME2
Wife: I see, DATETIME2 has the flexibility to specify the precision. What does it mean?
Husband: It means, you specify how many digits you want to store after the decimal for nano second unit. You can say, it’s precision for fractional part of seconds in timestamp. This may range from 0 to 7. Default is 7 e.g.
Wife: That’s cool. I don’t need to store fractional part of seconds so I can define it as DATETIME2(0)
Husband: Awesome!
Wife: But, Why this is coming in as a recommendation to use DATETIME2 by default?
Husband: This is ANSI and ISO compliant which are more of industry standards and makes it portable
Wife: Got it! Are there any other benefits of using DATETIME2 over DATETIME
Husband: Yes, since you can define the precision that you need in DATETIME2, it consumes less storage depending on how much precision you define. Storage need range from 6 to 8 bytes (+1 byte to store your precision definition). This is in contrast to flat 8 bytes needed for DATETIME
Wife: Amazing! Any other exciting reason to use DATETIME2?
Husband: This is more accurate too when it comes to storing milliseconds part. Here the precision is rounded in the unit of 100 nanoseconds in contrast to DATETIME which rounds off in the unit of 000, 003 or 007 nanoseconds
Wife: Perfect! Anything else you would like to share further?
Husband: At a minimum, you should know this also that DATETIME can go farthest in the history until 1753-01-01 but DATETIME2 can help you store any date in the history since 0001-01-01
Wife: That’s also good to know for any legacy implementations still running against DATETIME. Never thought, there was so much to learn while just defining the type of a column
Husband: Enjoy!
Wife: Is that all or you still have something else to say?
Husband: That’s pretty much out of it.
Just before concluding this post, here is the SQL code of the examples shown in above snapshots –
-- One
DECLARE @DefaultPrcision DATETIME2 = GetDate()
, @Precision5 DATETIME2(5) = GetDate()
, @PrecisionZero DATETIME2(0) = GetDate()
SELECT @DefaultPrcision AS "Default Precision"
, @Precision5 AS "Precision 5"
, @PrecisionZero AS "Precision Zero"
GO
-- Two
DECLARE @TimestampExample2 DATETIME2 = '2021-03-02 08:56:21.9911111'
SELECT @TimestampExample2 AS "Timestamp"
, CAST(@TimestampExample2 AS DATETIME2(3)) AS "DATETIME2 NOT Rounded AS Not Needed"
, CAST(@TimestampExample2 AS DATETIME) AS "DATETIME Rounded to 000 Even When Not Needed"
GO
-- Three
DECLARE @TimestampExample1 DATETIME2 = '2021-03-02 08:56:21.9955555'
SELECT @TimestampExample1 AS "Timestamp"
, CAST(@TimestampExample1 AS DATETIME2(3)) AS "DATETIME2 Rounded to 100" -- More Accurate
, CAST(@TimestampExample1 AS DATETIME) AS "DATETIME Rounded to 007" -- Less Accurate