entity framework, migrate from datetime to datetimeoffset

Question

what we want:

We have a column in a table in a sql server database, whose data type is datetime. We decided to change data type of this column into datetimeoffset(7).

what we have got so far:

-first approach:

The way mostly suggested to do this (as can be seen here) is to:

1. Add a new column with your new type

2. Use Sql() to take over the data from the original column using an update statement

3. Remove the old column

4. Rename the new column

-second approach:

but there is another solution to do this job, (that can be seen here) which we adapted like this:

    // this is part of our migration file:

    public override void Up()
    {
        AlterColumn("_TableName_", "_ColumnName_", c => c.DateTimeOffset(nullable: true, precision: 7));
        Sql(@"
update Tralus.MelkRadar.Crawl
set _ColumnName_ = todatetimeoffset(_ColumnName_, '+04:30')          
");
    } 

• AlterColumn changes data type in database, with keeping data and setting offset part of data to 00:00
• the Sql() statement after that, corrects the offset part.

the questions are:

Is there any important thing to take care of while taking the second approach?
Is there any reason why people prefer the first approach, though the second one is easier to do? Is there any reason that makes you suggest one of these approaches over the other one?

Answer 1

If you need to deal with historical datetime values, that might be during local daylight saving time, I'd go with option 2. Alter the column type, then use sql to update the value;

... _ColumnName_ = cast(_ColumnName_ as datetime2(7)) AT TIME ZONE '<name of timezone>'