CLF 日付書式を日付型に変換する【関数化・SQL Server編】

2021年9月3日

「CLF 日付書式を日付型に変換する」で投稿した内容をそのまま関数化することは「「副作用のある演算子を関数内で使用するのは無効」を回避することはできない？」にて手詰まりになりました。結局、今回のテーマだと関数化するのであれば「副作用のある演算子」を使わずに回避するしかないということです。
ということで今回は別の方法で関数化してみました。
関連記事と同じく対象とする日付時刻の文字列は「02/Sep/2021:14:32:19」という形式であることを前提としています。

1. CLF日付書式変換の関数化
- 1.1. 関連

CLF日付書式変換の関数化

実は、LANGUAGE を変更しなくても月名称（英略称）が数値化され、月と日が入れ替わっていれば（＝「日/月/年」ではなく「月/日/年」であれば） CONVERT で認識してくれるのです。

CREATE FUNCTION TO_DATETIME(@dtStr VARCHAR(max) )
RETURNS DATETIME
AS
BEGIN
  DECLARE @dtOut DATETIME
  DECLARE @pos1 INT = CHARINDEX('/',@dtStr)
  DECLARE @pos2 INT = CHARINDEX('/',@dtStr,CHARINDEX('/',@dtStr)+1)
  DECLARE @dayStr VARCHAR(max) = LEFT(@dtStr, @pos1 - 1)
  DECLARE @mntStr VARCHAR(max) = SUBSTRING(@dtStr, @pos1 + 1, @pos2 - @pos1 - 1)
  
  SET @dtStr = STUFF(@dtStr, CHARINDEX(':',@dtStr),1,' ')
  SET @dtStr = @mntStr + '/' + @dayStr + '/' + SUBSTRING(@dtStr,@pos2 + 1,LEN(@dtStr))

  SELECT @dtOut = TRY_CONVERT(DATETIME,REPLACE(@dtStr,MONTH_NAME,MONTH_NUMBER))
    FROM ( VALUES
      (N'Jan', 1),(N'Feb', 2),(N'Mar', 3),(N'Apr',  4),(N'May',  5),(N'Jun', 6),
      (N'Jul', 7),(N'Aug', 8),(N'Sep', 9),(N'Oct', 10),(N'Nov', 11),(N'Dec', 12)
    ) MONTH_TABLE( MONTH_NAME,MONTH_NUMBER )
   WHERE CHARINDEX(MONTH_NAME,@dtStr)>0

  RETURN @dtOut
END

「日」と「月」を入れ替えていますので少し煩雑に見えるかもしれません。@pos1 は最初の"/"の位置で、@pos2 は2番目の"/"の位置になります。なので、@pos1 までが「日」で、@pos1 と @pos2 の間が「月」ということになります。
月と年を入れ替えてしまえば、「月名称の変換表を取得する」で紹介した変換表で月名称を数値に変換して、あとは CONVERT に任せます。TRY_CONVERT を利用していますので、変換に失敗した場合は NULL が返ります。テストしてみます。

SELECT
   DTSTR
 , dbo.TO_DATETIME(DTSTR) RESULT
 FROM ( VALUES
  (N'01/Jan/2021:09:10:25')
 ,(N'29/Feb/2020:19:59:29') -- 閏年
 ,(N'01/Mar/2021:14:32:19')
 ,(N'01/Apr/2021:00:00:00')
 ,(N'01/May/2021:00:00:00')
 ,(N'01/Jun/2021:00:00:00')
 ,(N'01/Jul/2021:00:00:00')
 ,(N'31/Aug/2021:00:00:00')
 ,(N'30/Sep/2021:00:00:00')
 ,(N'31/Oct/2021:00:00:00')
 ,(N'30/Nov/2021:00:00:00')
 ,(N'31/Dec/2021:00:00:00')
 ,(N'29/Feb/2021:19:59:29') -- 閏年ではない（存在しない）
 ,(N'30/XXX/2021:19:59:29') -- 月名称が変換できない
) TEST_TABLE( DTSTR )