文字列から日時を変換するときにエラーメッセージ変換に失敗しました

debugcn 投稿 Dev

スタックトレース

    ALTER  PROCEDURE [dbo].[TEST_01]       
     (
        @StartDate  DateTime,
        @EndDate    DateTime
     )
    AS      
    BEGIN      
      SET NOCOUNT ON;  
      Declare @sql as nvarchar(MAX);
      SET @sql = @sql + ';WITH CTE_ItemDetails
                MAX(D.Name) as Name,
                SUM(ISNULL(DT.col1, 0)) AS col1,
                SUM(ISNULL(DT.col2, 0)) AS col2,
                SUM(ISNULL(DT.col3, 0)) AS col3,
                GROUPING(D.ItemType) AS ItemTypeGrouping
            FROM Items D
                INNER JOIN Details DT ON DT.ItemId = D.ItemId
                INNER JOIN Report R ON R.ReportId = DT.ReportId
                where 1=1'
            SET @sql = @sql + ' AND (R.ReportDate >= ' + @StartDate + 'AND  R.ReportDate <=' + @EndDate +')' 

            IF @someOtherVariable is not  null
            SET @sql = @sql + ' AND R.someColumn IN (''' +replace(@someOtherVariableValues,',','')+''+')' 

            SET @sql = @sql + 'SELECT col1, col2, col3 FROM CTE_ItemDetails'
            EXECUTE (@sql)  
    END

上記のT-SQLコードに似たストアドプロシージャがあります。（私が得ているエラーに関係がないと感じる多くのコードを削除したことに注意してください）それを実行すると、以下のエラーが発生します。

文字列から日時を変換するときに変換に失敗しました。

私のパラメータは以下の形式の値を持っています

exec TEST_01 @StartDate=N'4/1/2016 12:00:00 AM',@EndDate=N'4/30/2016 12:00:00 AM'

以下の行でSQLステートメントを動的に設定する方法に問題があるようです

SET @sql = @sql + ' AND (R.ReportDate >= ' + @StartDate + 'AND  R.ReportDate <=' + @EndDate +')'

エラーを回避するために適用できる最適な日付形式は何ですか。

ゴードン・リノフ

を介してパラメータを使用する必要がありますsp_executesql。

しかし、あなたの当面の問題はこの行です：

    SET @sql = @sql + ' AND (R.ReportDate >= ' + @StartDate + 'AND  R.ReportDate <=' + @EndDate +')'

次のようになります。

SET @sql = @sql + ' AND (R.ReportDate >= ''' + convert(varchar(10), @StartDate, 121) + ''' AND R.ReportDate <= ''' + convert(varchar(10), @EndDate, 121) +''')' ;

文字列への明示的な型キャストと二重一重引用符が含まれているため、日付リテラルは2016 - 04 - 14（つまり、2000）として解釈されないことに注意してください。

パラメータを使用するより良い方法は次のようになります。

    SET @sql = @sql + ' AND (R.ReportDate >= @StartDate AND R.ReportDate <= @EndDate)' ;

. . .
exec sp_executesql @sql, N'@StartDate date, @EndDate date)', @StartDate = @StartDate, @EndDate = @EndDate;

SQLステートメントを読む方が簡単です。タイプの問題はパラメーターを介して処理されます。また、クエリプランはより簡単に隠されます。残念ながら、パラメータは定数に対してのみ機能し、たとえば列名やテーブル名に対しては機能しません。

この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。

侵害の場合は、連絡してください[email protected]