「Conversion failed when converting date and/or time from character string while inserting datetime」エラーの解決方法

SQL Serverで文字列から日付時刻への変換に失敗する原因と解決策

SQL Serverで文字列をdatetime型に変換する際に、以下のエラーが発生することがあります。

Conversion failed when converting date and/or time from character string.

このエラーは、文字列がdatetime型に変換できない形式であることが原因です。

原因

このエラーが発生する主な原因は以下の3つです。

1. 文字列形式が不正

   文字列がdatetime型の標準フォーマットに準拠していない場合、変換に失敗します。標準フォーマットは以下の通りです。

   • YYYY-MM-DD HH:mm:ss.sss
   • YYYY-MM-DD

   例：

   -- 標準フォーマットに準拠しているため、変換成功
   INSERT INTO table (datetime_col) VALUES ('2024-03-24 08:48:00');
   
   -- 標準フォーマットに準拠していないため、変換失敗
   INSERT INTO table (datetime_col) VALUES ('2024/03/24');
   

2. 文字列に不正な文字が含まれている

   文字列に数字、ハイフン、コロン以外の文字が含まれている場合、変換に失敗します。

   -- 文字列に不正な文字が含まれているため、変換失敗
   INSERT INTO table (datetime_col) VALUES ('2024-03-24 08:48:00abc');
   

3. ロケールの設定が異なる

解決策

このエラーを解決するには、以下の方法を試してください。

1. 文字列形式を確認する

4. 変換関数を用いる

   CONVERT()やCAST()などの変換関数を用いて、文字列をdatetime型に変換することができます。これらの関数は、文字列形式を指定したり、ロケール設定の影響を受けずに変換を行うことができます。

   -- CONVERT()関数を使用して、文字列を標準フォーマットに変換
   INSERT INTO table (datetime_col) VALUES (CONVERT(datetime, '2024/03/24', 103));
   
   -- CAST()関数を使用して、文字列をdatetime型に変換
   INSERT INTO table (datetime_col) VALUES (CAST('2024-03-24 08:48:00' AS datetime));
   

5. TRY_CONVERT()関数は、文字列の変換が成功した場合のみdatetime型を返し、失敗した場合にはNULLを返します。

   -- TRY_CONVERT()関数を使用して、変換が成功した場合のみdatetime型を返す
   INSERT INTO table (datetime_col) VALUES (TRY_CONVERT(datetime, '2024/03/24'));
   

-- 標準フォーマットに準拠している文字列
DECLARE @str1 VARCHAR(20) = '2024-03-24 08:48:00';

-- CONVERT()関数を使用して、文字列を標準フォーマットに変換
INSERT INTO table (datetime_col) VALUES (CONVERT(datetime, @str1, 103));

-- CAST()関数を使用して、文字列をdatetime型に変換
INSERT INTO table (datetime_col) VALUES (CAST(@str1 AS datetime));

-- TRY_CONVERT()関数を使用して、変換が成功した場合のみdatetime型を返す
INSERT INTO table (datetime_col) VALUES (TRY_CONVERT(datetime, @str1));

-- 標準フォーマットに準拠していない文字列
DECLARE @str2 VARCHAR(20) = '2024/03/24';

-- 変換に失敗するため、エラーが発生
INSERT INTO table (datetime_col) VALUES (@str2);

-- エラーメッセージ
-- Conversion failed when converting date and/or time from character string.

解説

• CONVERT()関数: 第1引数にデータ型、第2引数に変換する文字列、第3引数にスタイル番号を指定します。スタイル番号103は、標準フォーマットを表します。
• CAST()関数: 第1引数に変換する文字列、第2引数にデータ型を指定します。

補足

• 上記のコードは、SQL Server 2017以降で動作します。
• 文字列の形式やロケール設定によっては、変換結果が異なる場合があります。

文字列をdatetime型に変換するその他の方法

SUBSTRING()関数とDATEADD()関数を使用して、文字列を部分文字列に分割し、DATEADD()関数で日付と時刻を組み立ててdatetime型に変換することができます。

DECLARE @str VARCHAR(20) = '2024-03-24 08:48:00';

-- SUBSTRING()関数を使用して、文字列を部分文字列に分割
DECLARE @year VARCHAR(4) = SUBSTRING(@str, 1, 4);
DECLARE @month VARCHAR(2) = SUBSTRING(@str, 6, 2);
DECLARE @day VARCHAR(2) = SUBSTRING(@str, 9, 2);
DECLARE @hour VARCHAR(2) = SUBSTRING(@str, 12, 2);
DECLARE @minute VARCHAR(2) = SUBSTRING(@str, 15, 2);
DECLARE @second VARCHAR(2) = SUBSTRING(@str, 18, 2);

-- DATEADD()関数を使用して、日付と時刻を組み立ててdatetime型に変換
INSERT INTO table (datetime_col) 
VALUES (DATEADD(dd, @day - 1, DATEADD(mm, @month - 1, DATEADD(yy, @year - 1900, 0)))) + 
       DATEADD(hh, @hour, DATEADD(mi, @minute, DATEADD(ss, @second, 0)));

PATINDEX()関数とLTRIM()関数を使用して、文字列から日付と時刻のパターンを抽出することができます。

DECLARE @str VARCHAR(20) = '2024-03-24 08:48:00';

-- PATINDEX()関数を使用して、日付と時刻のパターンを抽出
DECLARE @year INT = PATINDEX('%[0-9][0-9][0-9][0-9]%', @str);
DECLARE @month INT = PATINDEX('%[0-9][0-9]%', @str, @year + 5);
DECLARE @day INT = PATINDEX('%[0-9][0-9]%', @str, @month + 3);
DECLARE @hour INT = PATINDEX('%[0-9][0-9]%', @str, @day + 3);
DECLARE @minute INT = PATINDEX('%[0-9][0-9]%', @str, @hour + 3);
DECLARE @second INT = PATINDEX('%[0-9][0-9]%', @str, @minute + 3);

-- LTRIM()関数を使用して、抽出結果の空白を除去
SET @year = LTRIM(@year);
SET @month = LTRIM(@month);
SET @day = LTRIM(@day);
SET @hour = LTRIM(@hour);
SET @minute = LTRIM(@minute);
SET @second = LTRIM(@second);

-- 抽出結果を使用してdatetime型を生成
INSERT INTO table (datetime_col) 
VALUES (DATEADD(dd, @day - 1, DATEADD(mm, @month - 1, DATEADD(yy, @year - 1900, 0)))) + 
       DATEADD(hh, @hour, DATEADD(mi, @minute, DATEADD(ss, @second, 0)));

DATETIME_FROM_PARTS()関数を使用する (SQL Server 2016以降)

SQL Server 2016以降では、DATETIME_FROM_PARTS()関数を使用して、個々の要素からdatetime型を生成することができます。

DECLARE @str VARCHAR(20) = '2024-03-24 08:48:00';

-- DATETIME_FROM_PARTS()関数を使用して、個々の要素からdatetime型を生成
INSERT INTO table (datetime_col) 
VALUES (DATETIME_FROM_PARTS(@year, @month, @day, @hour, @minute, @second));

OPENROWSET()関数とsp_OACreate()関数を使用して、OLE Automationオブジェクトを介して文字列をdatetime型に変換することができます。

DECLARE @str VARCHAR(20) = '2024-03-24 08:48:00