0

We have a database with over 100 tables which has already been deployed to tens of production sites. Initially all datetime columns were of type datetime, but as the system evolved we started to use datetime2 for new modules/features. We now sometimes run into precision issue when comparing values from different tables and need to cast datetime2 when comparing to datetime.

The system uses C# and Entity Framework for 90% of business logic and stored procedures for the remaining 10%. The system stores a lot of time-series and event-type information and there's business logic that relies on for example:

  1. end_time_utc (datetime) having matching start_time_utc (datetime) in other rows
  2. datetime type column in 1 table having a matching value in datetime type column in another table

I'm considering migrating all datetime columns to datetime2. I know how to write a script to do it, but I'm wondering what the dangers are and what problems people run into when doing this?

PiotrS
  • 180
  • 3
  • 16
  • 2
    For one, you'll have to rebuild indexes that include those columns and recreate constraints that might depend on those columns. – squillman Nov 16 '18 at 22:33
  • 3
    Any queries that say things like `column + 1` or `column - 7`, or where you perform that shorthand with parameters or variables that you also change when you change the columns, [will break](https://sqlblog.org/2011/09/20/bad-habits-to-kick-using-shorthand-with-date-time-operations). That is hard to discover, too, since searching your codebase for `[anything] +|- [anything]` will yield a boatload of false positives. – Aaron Bertrand Nov 16 '18 at 23:59
  • 1
    In any case, you should be able to test this by restoring a backup (you're taking those, right?) on a test server (you have one of those, right?), changing all the columns, and running your application with a workload representing your entire business cycle. You'll find what breaks pretty quickly, and the answer may be nothing, but it will be hard for us to answer what only testing can. – Aaron Bertrand Nov 17 '18 at 00:02
  • @AaronBertrand: I don't think we use such shorthands anywhere, but it is a good point. – PiotrS Nov 17 '18 at 22:24
  • Also, you might want to start small - don't change everything at once, but change the easy parts first - the less used tables. Of course, any change must be made on a test environment and thoroughly tested before applied on production. – Zohar Peled Nov 19 '18 at 07:13

0 Answers0