You want to transform your system from one state to another. The initial state is when DateTime is used everywhere. The target state is when DateTimeOffset is used everywhere. You want to do it smoothly and introducing as little changes as possible. This might be a plot for the most interesting task to a dead  end. DateTime type was the default .NET type to capture date/time values and usually the logic built around them was made like it will last forever. Changes of existing fields will produce cascadian changes with effect on almost all parts of the enterprise system. In some critical case you might end up with changing about 200 stored procedures only for one field. This is the first problem. The second problem is that the consequences of such changes are hard to be found during testing. The regression testing does not guarantee that you won’t miss anything or the system will be functional for any cases. The required QA efforts will be increasing as you work and you won’t have a clear understanding when it will end.

During my investigation I found a possible approach for such systems which has three steps. The approach is based on the assumption that the system is not currently support time zones and all subsystems are located in the same time zone.

1. Add a paired computed field for reading DateTimeOffset values from DB.
2. Make transformation around operations which READS date/time from DB in client code.
3. Make transformation around operations which WRITES date/time from DB in client code.

This approach will help to localize the changes and limit QA efforts. It also provides good predictability for estimation of the future work. I described the steps with more technical details below.

Failed Approach

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

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

Whilst in DB the conversion from DateTime to DateTimeOffset and back is supported at very good level and is not a big deal, in C# code this is complicated because of the strong typing. You cannot read DateTime value if a DB routine returns DateTimeOffset value. When you change a returning type for one field then all places where it is used in the whole enterprise system should be modified. In some cases it’s just impossible because you may don’t know about some places if the system is very big. This is the reason the approach with just changing the type of field won’t work. You may try to find all usages of specific column 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 do the transformation it’s important to predict upfront what parts of the system will be affected. You should have an approach to localize the changes in specific module of the system without breaking the system at all.

Better Approach

It’s just better, not the best one. I still expect some issues might appear in the future, but it’s comparatively more safe than the previous one. The main difference is that you’re not doing the transformation in one single step. There is a sequence of dependent changes which will give you the control over efforts.

Paired Computed Field

When you add a computed paired column to DB you’ll introduce a new field with required type. It will allow you to segregate reading, writing and separate updated code from the old one. This operation easily can be done with the script and no QA efforts required.

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 slice your system into sections where you’d like to introduce DateTimeOffset. Now you can use the new type only in a single stored procedure without need to change all related places.

Transformation of Reads

The reading operations appeared the most difficult for the transformation because of the approach which is used for integration between the client code and DB routines. Date/time values are passed via string serialization. DateTimeOffset has different format and cannot be read by default to DateTime variables on the client side. The write operations just work. If you pass DateTime value to DateTimeOffset argument or field the value will be accepted with the assumption that it’s UTC adjusted value. The time offset after conversion will be “+00:00”.

Now you may take some section of your system and find exact number of DB routines returning DateTime to the client code. Here you want to change read operations in the client code to read DateTimeOffset values. You also will need to change DB routines to make sure they return values from the new computed fields. The expected result of this step looks as follows:

• The client code reads DateTimeOffset and use this type wherever possible to expose values from the system.
• The DB routines use DateTimeOffset in arguments and the client code passes DateTimeOffset value to them.
• The new type is used internally in DB routines.
• DB routines return value from the computed fields.

In the end you’ll get the system which reads from new fields when stores values via the old one. At this moment once you capture the time offset in write operations the whole system will start to work correctly with time zones.

Transformation of Writes

Now your system should capture the time offset and send it to DB to store in the fields. Section by section, routine by routine you should take the old field and change it to be computed from the new one.  You’re already reading from them, now you’re storing values and keep the old one for the backward compatibility. This approach will help you to isolate changes only for specific section. The expected result looks as follows:

• the client code creates DateTimeOffset values and passes them to DB routines
• the new fields are now real fields with values
• the old fields are now computed fields
• DB routines stores values to the new fields

In the end you’ll get the system which writes and reads new type DateTimeOffset. This type has built-in support of time offset so that you won’t need to do any manual conversion to UTC.

Wrapping up

It’s up to you how to slice the system to provide isolation according to routines which they’re using. You’ll make all efforts predictable which help to estimate them upfront. This is no doubt still may introduce issues but they won’t grow like a snowball as you work. Later you can get rid of the old fields and even more introduce more source of time offsets. They might be user preferences or some other configurations. Below I put information about compatibility of two types for references.

• Changing of column type just works assuming values in UTC. The time offset +00:00 will be added implicitly. If you need to specify another time zone consider the solution with a temp column.
• Formatting just works.
• Comparison just works.
• SYSDATETIMEOFFSET() will just work instead of GETDATE()
• Any assignment from DateTimeOffset to DateTime and vise versa will just work.