SQL CLRを作るときにDateTimeOffsetを使いたいときのためのメモ

SQL CLRとは

SQL CLRは、ストアドプロシージャ、トリガー、ユーザ定義関数、ユーザ定義型、ユーザ定義集計をC#などの言語で作成できる仕組みです。
これを用いることでSQL言語では実現困難な関数などを作成できます。

DateTimeOffsetを使いたいときの問題点

言語をVB.NETとして、ユーザ定義関数を定義する場合こんな形になります。

sample.vb

Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Public Class Test

    ' JapaneseLocale
    ' https://docs.microsoft.com/en-us/openspecs/office_standards/ms-oe376/6c085406-a698-4e12-9d4d-c3b0ee3dbc4a
    Private Const cJapaneseLocaleID As Integer = 1041

    ''' <summary>
    ''' 半角文字を全角文字に変換する関数
    ''' </summary>
    ''' <param name="vStr">変換元の文字列</param>
    ''' <returns>変換後の文字列</returns>
    <SqlFunction(
        DataAccess:=DataAccessKind.None,
        IsDeterministic:=True,
        IsPrecise:=False,
        SystemDataAccess:=SystemDataAccessKind.None,
        Name:="StrConvToWide"
    )>
    Public Shared Function StrConvToWide(ByVal vStr As SqlString) As SqlString
        Dim ans As SqlString
        If vStr.IsNull Then
            ans = SqlString.Null
        Else
            Dim str As String = Strings.StrConv(vStr.Value, VbStrConv.Wide, cJapaneseLocaleID)
            If IsNothing(str) Then
                ans = SqlString.Null
            Else
                ans = New SqlString(str)
            End If
        End If
        Return ans
    End Function
End Class

SQLServerにインストールするのに次のスクリプトを実行します。

install.sql

-- 半角を全角に変換する関数
CREATE FUNCTION dbo.CLR_ConvStr_StrConvToWide(
    @string NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME Assem_ConvZenkaku.CLR_ConvZenkaku.StrConvToWide;
GO

上記のソースの中で、CLRの外部と値をやり取りする際に文字列であれば

xx as SqlString

と指定しています。

同様に他の型をやり取りする場合はSqlxxxxの形で型を宣言します。
Sqlxxxの定義はSystem.Data.SqlTypeで行われています。

この中に、DateTimeOffsetの定義は含まれていません。

DateTimeOffsetを渡したい場合はどうすればいいのでしょうか。

DateTimeOffsetを使うには

いろいろ試行した結果、受け渡しできたやり方は次のとおりでした。

UseDateTimeOffset.vb

Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Public Class Test
    <SqlFunction(
        DataAccess:=DataAccessKind.None,
        IsDeterministic:=True,
        IsPrecise:=True,
        SystemDataAccess:=SystemDataAccessKind.None,
        Name:="ConvTimezone"
    )>
    Public Shared Function ConvTimezone(vDateTimeOffset As DateTimeOffset?) As DateTimeOffset?
        If vDateTimeOffset.HasValue = False Then
            Return New DateTimeOffset?(Nothing)
        End If

        Return New DateTimeOffset?(vDateTimeOffset.Value.ToUniversalTime())
    End FunctionEnd Class

SQLServerにインストールするのに次のスクリプトを実行します。

install.sql

CREATE FUNCTION dbo.ConvTimezone(
    @vdate datetimeoffset
)
RETURNS datetimeoffset
AS EXTERNAL NAME [Assem_test].CLR_test.ConvTimezone;
GO

結論

SqlDateTimeOffset

の代わりに

DateTimeOffset?

とすればよい。