-1

What are the advantages of having a column of type datetime2 in SQL Server instead of a string? Obviously strings are much more flexible. Are there performance advantages while querying?

I'm designing a database where all entries are added via functions in another language and so there's no concern that a value not formatted like a date will be added to the column in question. I'm considering making that column type string so I can more easily parse them when querying. When they are added to the database they are converted from Python's datetime format, which by convention has six digits of precision in fractions of seconds, to datetime2 format which has seven. Making the column be of type string will stop that from happening, but I'm worried about the performance issues that might come with not using datetime2.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
japem
  • 1,037
  • 5
  • 16
  • 30
  • can you provide an example of how you would parse a string? It seems to me that indexing a datetime datatype will be much more efficient when you have to search dates - `WHERE BETWEEN '1/1/2019' AND '1/7/2019'`. Share how you would do that if it was a string datatype. – Greg Feb 22 '19 at 20:19
  • 2
    Use the right type from the beginning (datetime2). 1. It's short-sighted to say that "all entries are added via functions in another language." That may be true today, but you don't know every way the DB might be written to in the future. If you make the type a string, the DB can no longer natively enforce that valid dates are being stored. 2. With a datetime2 type, you can easily output it to whatever string format you like, either in your query, or in your client app. 3. datetime2 is stored as a numeric value, making comparisons and difference computing easy/fast. – Richard II Feb 22 '19 at 20:21
  • Sorry, should have made that clear: format is YYYY-MM-DD HH:MM:SS.ssssss(s). So one date being after another corresponds directly to the string value being greater. I'd have `WHERE timestamp BETWEEN '2018-09-18 05:34:22.125512' AND '2018-09-18 05:35:737282'`, for example. – japem Feb 22 '19 at 20:23
  • 2
    What is the advantage of using a database when you could use text files? – Salman A Feb 22 '19 at 20:24
  • @SalmanA performance. Which is my question. Is there a performance advantage using datetime2. Richard thanks for a real answer. – japem Feb 22 '19 at 20:26
  • There should be. A datetime2 will certainly use smaller space (8bytes tops vs 27bytes), behind the scenes they're stored as numbers (number comparison is faster than string) and then you have dedicated functions for manipulating the dates. PS: use datetime2(6) to have 6 digit fractional seconds. – Salman A Feb 22 '19 at 20:31
  • https://stackoverflow.com/questions/4759012/when-to-use-varchar-and-date-datetime – Dale K Feb 22 '19 at 20:54
  • 1
    Have a search on SO for people asking how to deal with issues arising from storing dates as strings... they are extremely common. There are never any issues when storing dates as dates :) just the (easy) question of formatting them. – Dale K Feb 22 '19 at 21:10

1 Answers1

4

Datetime2 (and other date/time types):

  • will save you storage space and that will eventually become a performance gain.
  • will allow you to easily do date math without having to create procedures that need to figure if February will have 28 or 29 days (just to give an example).
  • have their built-in constraints so you don't have to worry about someone inserting an invalid date.
  • can be easily converted to different formats
  • can have their parts extracted, including weeks, weekdays or days of the year.
  • and so on...
Luis Cazares
  • 3,495
  • 8
  • 22