SQL SERVER: Converts sql server datetime and datetime2 columns to datetimeoffset

AlterDateTimeColumnsToDateTimeOffset.ps1

Clear-Host
$module = Get-Module tcdbtools 
if (!$module) {
    Install-Module tcdbtools  -Force 
}
Import-Module tcdbtools
$module = Get-Module SqlServer 
if (!$module) {
    Install-Module SqlServer  -Force 
	#get-command -Module sqlserver
}
Import-Module SqlServer
$creds = $null

<# ***************************************************************************
**** CHANGE THESE VARIABLES TO POINT AT THE TARGET SERVER / DATABASE
*************************************************************************** #>

$server = "ServerName"
$database = "Database"


<#
# Run this line to add user credentials after changing all of the values, or add the credential manually in the Credential Manager
# Do not save password into this script file. :D
Set-DBUserCredential -ApplicationName "MyServerCreds" -UserName "UserName" -Password "Password"
#>
<# Comment out $creds if wanting to use trusted #>
# $creds = Get-DBUserCredential -ApplicationName "laair_prod"

$handler = [Microsoft.Data.SqlClient.SqlInfoMessageEventHandler] {
    param($sender, $event) 
    if ($event.Message -inotmatch "Changed database context to") {
        Write-Host $event.Message 
    }
};

if ($creds) {
    $ServerConnection = [Microsoft.SqlServer.Management.Common.ServerConnection]::new($server, $creds.UserName, $creds.Password)
    $ServerConnection.DatabaseName = $database
} else {
    $ServerConnection = [Microsoft.SqlServer.Management.Common.ServerConnection]::new()
    $ServerConnection.ConnectionString = "data source=$server;initial catalog=$database;trusted_connection=true;encrypt=optional;"
}

$ServerConnection.add_InfoMessage($handler); 
$serverInstance = [Microsoft.SqlServer.Management.Smo.Server]::new($ServerConnection)

$db = $serverInstance.Databases[$database]

# this is slow as crap.... much faster to use the query
#$columns = $db.Tables.Columns
#$dateColumns = $columns | 
#    Where-Object { $_.Datatype.SqlDataType -ieq "datetime" -or $_.Datatype.SqlDataType -ieq "datetime2" } |
#    Sort-Object -Property @{Expression={ [string]$_.Parent.Schema }}, @{Expression={ [string]$_.Parent.Name }}

$query = "
SELECT [fn].[schema_name], [fn].[table_name], [c].[name] AS [column_name], [t].[name] AS [type_name] 
FROM [sys].[columns] AS [c] 
INNER JOIN [sys].[types] AS [t] ON [t].[system_type_id] = [c].[system_type_id]
CROSS APPLY (
	SELECT OBJECT_SCHEMA_NAME([c].[object_id]) AS [schema_name], OBJECT_NAME([c].[object_id]) AS [table_name]
) AS [fn]
WHERE 1 = 1
	AND [t].[name] IN ('datetime2', 'datetime')
	--AND [t].[name] = 'datetimeoffset'
	AND OBJECTPROPERTY([c].[object_id], 'IsMSShipped') = 0
	AND OBJECTPROPERTY([c].[object_id], 'IsUserTable') = 1
ORDER BY [fn].[schema_name], [fn].[table_name], [c].[name]"

$SqlCmdArguments = @{
    ServerInstance = $server
    Database = $Database
    ApplicationName = $MyInvocation.MyCommand.Name
    Encrypt = "Optional"
    Query = $query
}
if ($creds) {
    $SqlCmdArguments.Add("Credential", $creds)
}

$columnsList = Invoke-Sqlcmd @SqlCmdArguments 

$Scripter = [Microsoft.SqlServer.Management.Smo.Scripter]::new($serverInstance)
$Scripter.Options.AllowSystemObjects = $False
#$Scripter.Options.IncludeIfNotExists = $true
$Scripter.Options.IncludeHeaders = $False
$Scripter.Options.ScriptSchema = $true
$Scripter.Options.Indexes = $True
$Scripter.Options.WithDependencies = $False
$Scripter.Options.Encoding = [System.Text.Encoding]::UTF8

# enum the tables so we can use .Item()
# $db.EnumObjects([Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::Table) | Out-Null
try { 
    foreach ($column in $columnsList) {
        $table = $db.Tables.Item($column.table_name, $column.schema_name)
        $dateColumn = $table.Columns[$column.column_name]

        Write-Host "Working on [$($dateColumn.Parent.Schema)].[$($dateColumn.Parent.Name)].[$($dateColumn.Name)] ($($dateColumn.DataType.Name))" -ForegroundColor Yellow
        $indexes = $dateColumn.Parent.Indexes | Where-Object { $_.IndexedColumns.Name -icontains $dateColumn.Name }

        <# Script the drops for the defaults and the indexes tied to this column #>
        $Scripter.Options.ScriptDrops = $true    
        $dropSql = [System.Text.StringBuilder]::new()
        if ($dateColumn.DefaultConstraint) {
            $dropSql.AppendLine("RAISERROR('DROP DEFAULT CONSTRAINT [$($dateColumn.DefaultConstraint.Name)] ON [$($dateColumn.Parent.Schema)].[$($dateColumn.Parent.Name)]', 0, 1) WITH NOWAIT;") | Out-Null
            $dropSql.AppendLine($Scripter.Script($dateColumn.DefaultConstraint)) | Out-Null
        }
        foreach($index in $indexes) {
            $dropSql.AppendLine("RAISERROR('DROP INDEX $($index.IndexType) [$($index.Name)] ON [$($dateColumn.Parent.Schema)].[$($dateColumn.Parent.Name)]', 0, 1) WITH NOWAIT;") | Out-Null
            $dropSql.AppendLine($Scripter.Script($index)) | Out-Null
        }

        <# Script the creates for the defaults and the indexes tied to this column #>
        $Scripter.Options.ScriptDrops = $false    
        $createSql = [System.Text.StringBuilder]::new()
        if ($dateColumn.DefaultConstraint) {
            $dateColumn.DefaultConstraint.Text = "(SYSDATETIMEOFFSET() AT TIME ZONE 'UTC')"
            $createSql.AppendLine("RAISERROR('CREATE DEFAULT CONSTRAINT [$($dateColumn.DefaultConstraint.Name)] ON [$($dateColumn.Parent.Schema)].[$($dateColumn.Parent.Name)]', 0, 1) WITH NOWAIT;") | Out-Null
            $createSql.AppendLine($Scripter.Script($dateColumn.DefaultConstraint)) | Out-Null
            $dateColumn.Nullable = $false
        }    
        foreach($index in $indexes) {
            $createSql.AppendLine("RAISERROR('CREATE INDEX $($index.IndexType) [$($index.Name)] ON [$($dateColumn.Parent.Schema)].[$($dateColumn.Parent.Name)]', 0, 1) WITH NOWAIT;") | Out-Null
            $createSql.AppendLine($Scripter.Script($index)) | Out-Null
        }

        $ServerConnection.BeginTransaction()

        $db.ExecuteNonQuery($dropSql.ToString());

        Write-Host "ALTER [$($dateColumn.Parent.Schema)].[$($dateColumn.Parent.Name)].[$($dateColumn.Name)] DATETIMEOFFSET"
        $dateColumn.DataType = [Microsoft.SqlServer.Management.Smo.DataType]::new("DateTimeOffset")
        $dateColumn.DataType.NumericScale = 7
        $dateColumn.Parent.Alter()

        $db.ExecuteNonQuery($createSql.ToString());
    
        $ServerConnection.CommitTransaction()
    }
} catch {
    # bail out on the first exception and rethrow it so it does not keep spinning
    if ($ServerConnection.TransactionDepth -gt 0) {
        $ServerConnection.RollBackTransaction()
    }
    throw
}

Write-Host "DONE" -ForegroundColor Yellow

I have decided to start using datetimeoffsets in my databases now as that is much easier than getting everyone to change their code to store the values as UTC. For the cost of two extra bytes you can put a regular datetime value into a datetimeoffset column, and always get an accurate representation of the date using AT TIMEZONE even when its a much older date and daylight savings has changed.

Similar reasons as listed in this article: https://ardalis.com/why-use-datetimeoffset/