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:
- end_time_utc (datetime) having matching start_time_utc (datetime) in other rows
- 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?