Migrating Datetime to DateTimeOffset

Question

The time has come in the application I am working on to migrate DateTime fields in SQL Server to DateTimeOffset.

Was wondering if there were any automated tools in SQL Server that could help me with this so I don't need to script a bunch of alter table statements?

Answer 1

There are no such tools in SQL Server itself, however there 3rd party tools, that wrap up management tasks of a DB and generate change scripts for you.

In absence of a third party tool, you can write a script that will generate change scripts for you.

Here is a sample script, that finds all columns of datatime type and generate script to change it to DateTimeOffset data type.

select 
OBJECT_NAME (a.object_id) as Table_Name
,a.name as Colimn_Name
,b.name as Old_Datatype
,'ALTER TABLE ' + OBJECT_NAME (a.object_id) + ' ALTER COLUMN ' + a.name + ' DATETIMEOFFSET '   + 
CASE a.is_nullable
    when 1 then ' NULL '
    else ' NOT NULL '
END
from sys.columns  a
join sys.types b on a.user_type_id = b.user_type_id
join sys.tables c on a.object_id = c.object_id
where b.name = 'datetime'
and c.type = 'U'

Also you can use an undocumented sp_msForEachTable to execute code for each table in your database.