DateTime to DateTimeOffset Transition

Imagine that you want to convert your system from one state to another. The initial state is when DateTime is used everywhere, both in C# code and in the database. The final state is when DateTimeOffset is used everywhere. You want to make the transition smooth and make as few changes as possible. This description can be the beginning of a very interesting problem with a dead end at the end.

The DateTime type was the default .NET type for working with a date and time some time ago and the logic around it was usually built as if it would never change. If you try to change the type in one step, it will lead to cascading changes in almost all parts of the system. In extreme cases, it may be necessary to change about 200 stored procedures for only one field. This is the first problem. And the second problem is that the consequences of such changes are difficult to find during testing. Regression testing does not guarantee that you will not miss anything or the system will function in any cases. The necessary quality assurance efforts will increase as you work, and you will not have a clear understanding of when it will end.

During my research, I found a possible approach to such transformations. This approach has three stages and is based on the assumption that the system currently does not support time zones, and all subsystems are located in the same time zone.

1. Add a paired calculated field to read the DateTimeOffset values from the database.
2. Make the conversion of read operations.
3. Make the conversion of the write operations.

This approach will help to localize changes and limit the efforts spent on QA. It will also provide good predictability for evaluating future work. Below I described the stages in more detail.

Bad Approach

Imagine that there are about 150 fields associated with date/time values. You can use the following SQL script to find out the complete list in your database.

select
    tbl.name as 'table',
    col.name as 'column',
    tp.name as 'type',
    def.name as 'default'
from sys.columns col
    inner join sys.tables tbl on tbl.[object_id] = col.[object_id]
    inner join sys.types tp on tp.system_type_id = col.system_type_id
        and tp.name in ('datetime', 'date', 'time', 'datetime2', 'datetimeoffset', 'smalldatetime')
    left join sys.default_constraints def on def.parent_object_id = col.[object_id]
        and def.parent_column_id = col.column_id
order by tbl.name, col.name

While the database conversion from DateTime to DateTimeOffset and back is maintained at a very high level, it is difficult in the C# code because of the typing. You cannot read the DateTime value if the database returns the DateTimeOffset value. If you change the return type for one field, you must change all the places where it is used throughout the system. In some cases, this is simply impossible, because you may not know about some places if the system is very large. It is for this reason that an approach with a simple change of field type will not work. You can try to find all the uses of a particular table field in the database using the following script.

SELECT
    ROUTINE_NAME,
    ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%table%'
    OR ROUTINE_DEFINITION LIKE '%field%'
    AND ROUTINE_TYPE='PROCEDURE'

In order to make the transformation, it is important to predict in advance which parts of the system will be affected. You should have an approach to localizing changes in a particular module of the system without disturbing the rest of the parts.

Better Approach

This approach is simply “better”, not the best. I still expect that some problems may appear in the future, but it looks more secure than the previous one. The main difference is that you do not perform the conversion in one step. There is a sequence of dependent changes that will give you control over the situation.

Creating Calculated Field

When you add a calculated duplicating field to the database, you enter a new field with the required type. This will allow you to separate the reading and writing, and to separate the updated code from the old one. This operation can easily be performed using a script, and no efforts are required to ensure quality.

declare @table sysname, @column sysname, @type sysname, @default sysname

declare cols cursor for
select
    tbl.name as 'table',
    col.name as 'column',
    tp.name as 'type',
    def.name as 'default'
from sys.columns col
    inner join sys.tables tbl on tbl.[object_id] = col.[object_id]
    inner join sys.types tp on tp.system_type_id = col.system_type_id
        and tp.name in ('datetime', 'date', 'time', 'datetime2', 'smalldatetime')
    left join sys.default_constraints def on def.parent_object_id = col.[object_id]
        and def.parent_column_id = col.column_id
order by tbl.name, col.name

open cols
fetch from cols into @table, @column, @type, @default
while @@FETCH_STATUS = 0
begin
    declare @cmd nvarchar(max)
    set @cmd = 'alter table ['+@table+'] add ['+@column+'_dto] as todatetimeoffset(['+@column+'], ''+00:00'')'
    exec (@cmd)
    fetch from cols into @table, @column, @type, @default
end
close cols
deallocate cols

Based on the above result, you can cut your system into partitions where you want to enter DateTimeOffset. Now you can use the new type only in one stored procedure without having to change all the places associated with it.

Transforming Reading

Read operations turned out to be the most difficult to convert because of the approach that is used to integrate the client code and database. Date/time values are passed through string serialization. DateTimeOffset has a different format and cannot be read by default for DateTime variables on the client side. At the same time, write operations just work. If you pass the DateTime value to the argument or the DateTimeOffset field, this value will be accepted with the assumption that it is UTC corrected. The time offset after the conversion will be “+00:00”.

Now you can take a section of the system and determine the exact number of the files that return DateTime to the client code. We will have to change the read operations in the C# code to read the DateTimeOffset values. Also, we will need to change the stored procedure in the database so that they return values from the new calculated fields. The expected result of this step is as follows:

• C# code reads DateTimeOffset and uses this type wherever possible to return values from the system.
• Storage procedures of the database use DateTimeOffset in the arguments and the C# code passes the DateTimeOffset value to them.
• A new type is used inside the stored procedures of the database.
• Stored procedures of the database return values from the newly added fields.

Finally, you will get a system that reads data from the new fields, while stores the values in the old ones. Now, when the time offset is passed in the write operations and stored in the system, the entire system will start to work correctly with the time zones.

Transforming Records

Now we need to fix the time offset in the system, send it to the database and save it in the fields. It is necessary to take the old field and change it to the computed from the new one, and the new field must now contain the values. You already read from them, now you write the values to them, and the old ones, on the contrary, are read only. This approach will help you isolate changes only for a specific section. The expected result is as follows:

• The C# code creates the DateTimeOffset values and passes them to the database
• New fields are now real fields with values
• Old fields are now the calculated ones and used for readingf
• Databases stored procedures save values in new fields

Finally, we will get a system that writes and reads a new type of DateTimeOffset. This type has built-in support for time offset, so you do not need to do any manual conversion to UTC or between time zones in general.

Further Steps

The only recommendation that I can give regarding the division of the system into sections for conversion is as follows: it is necessary to provide sufficient isolation of the modules in accordance with the storages used. Thus, you will achieve predictability of efforts and will be able to assess them in advance. Undoubtedly, some problems can still arise, but they will not grow like a snowball in the course of work. Later you can get rid of the old fields. Information about the time zone can be derived from the operating system or user settings. Below is the information about the compatibility of the two types in the database.

• Changing the column type from DateTime to DateTimeOffset works with an implicit conversion. The time offset will be +00:00. If you want to specify a different time zone, you must use a temporary column.
• Formatting of string values is supported.
• All comparison operators are supported.
• SYSDATETIMEOFFSET() can replace GETDATE() without risk
• Any naming between DateTime and DateTimeOffset works with an implicit conversion.

It would be very interesting to hear stories about such a transformation from those who have already done this in their systems, and also how time zones are supported in their systems.

Further Reading:

• More about Introduction of Time zones in long-lived Project