How do I correctly pass DateTime from C# to SQL?

Question

I'm attempting to run a stored procedure from C# and am getting a conversion error while unit testing. Every search result for this issue I've seen indicated that using ToString() is not the preferred way to pass DateTime's.

C# Code:

using (SqlConnection lvConn = new SqlConnection(gvSQLConnectionS))
{
    lvConn.Open();
    SqlCommand lvCmd = new SqlCommand(gvSQLsp, lvConn);
    lvCmd.CommandType = CommandType.StoredProcedure;
    lvCmd.Parameters.Add(new SqlParameter(gvSQLparamFunction, gvSQLfunctionUpdLE));
    lvCmd.Parameters.Add(new SqlParameter(gvSQLparamID, lvAAAID));
    lvCmd.Parameters.Add(new SqlParameter(gvSQLparamName, lvName));
    lvCmd.Parameters.Add(new SqlParameter(gvSQLparamPath, lvPath)); 
    SqlParameter lvParameterLE = lvCmd.Parameters.Add(gvSQLparamLE, SqlDbType.DateTime);
    SqlDateTime lvDTnow = new SqlDateTime(DateTime.Now);
    lvParameterLE.Value = lvDTnow;
    lvCmd.ExecuteNonQuery();
}

SQL Setup: Last Executed (datetime, null)

Stored Procedure:

ALTER PROCEDURE [dbo].[sp_BackupCleaner]
    @lvFunction int,
    @lvAAAID varchar(12) = NULL,
    @lvAAAID_new varchar(12) = NULL,
    @lvName varchar(35) = NULL,
    @lvName_new varchar(35) = NULL,
    @lvPath varchar(255) = NULL,
    @lvPath_new varchar(255) = NULL,
    @lvLastExecuted datetime = NULL
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @lvSQL varchar(max)    

---

IF @lvFunction = 6
    BEGIN
        SET @lvSQL = 'UPDATE [dbo].[Backup_Paths]
                SET [dbo].[Backup_Paths].[Last Executed] = '+@lvLastExecuted+'
                WHERE [dbo].[Backup_Paths].[AAA Client ID] = '''+@lvAAAID+'''
                AND [dbo].[Backup_Paths].[Process Name] = '''+@lvName+'''
                AND [dbo].[Backup_Paths].[UNC Path] = '''+@lvPath+''''
    END

execute(@lvSQL)
END

Exception thrown:

System.Data.SqlClient.SqlException: Conversion failed when converting date and/or time from character string.

Answer 1

The problem is trying to concat the Datetime variable with the string literal using the + operator. If you are using SqlServer 2012 or higher, you can use CONCAT and this will fix the problem. (but probably make your query fail when that value is NULL)

CONCAT('select blah blah blah', @myDateTimeValue);

Answer 2

There's no need to use dynamic SQL. In general dynamic SQL should be avoided. It's very rare to find a case where dynamic SQL can't be replaced by something better.

Just write and run the statement you want :

IF @lvFunction = 6
    BEGIN
        UPDATE [dbo].[Backup_Paths]
            SET [dbo].[Backup_Paths].[Last Executed] = @lvLastExecuted
            WHERE [dbo].[Backup_Paths].[AAA Client ID] = @lvAAAID
            AND [dbo].[Backup_Paths].[Process Name] = @lvName
            AND [dbo].[Backup_Paths].[UNC Path] = @lvPath
    END

NOTE

If the stored procedure performs a lot of different tasks it's a strong sign that it should be broken into separate procedures.

Answer 3

When you do SET [dbo].[Backup_Paths].[Last Executed] = '+@lvLastExecuted+' that is just as bad as using .ToString() in C#.

If you are not further dynamically modifying @lvSQL you can directly execute the query

IF @lvFunction = 6
BEGIN
    UPDATE [dbo].[Backup_Paths]
            SET [dbo].[Backup_Paths].[Last Executed] = @lvLastExecuted
            WHERE [dbo].[Backup_Paths].[AAA Client ID] = @lvAAAID 
            AND [dbo].[Backup_Paths].[Process Name] =  @lvName
            AND [dbo].[Backup_Paths].[UNC Path] = @lvPath
END

If you are going to further edit the query you need to use sp_executesql and forward the parameters on to the dynamic query.

IF @lvFunction = 6
BEGIN
    SET @lvSQL = 'UPDATE [dbo].[Backup_Paths]
            SET [dbo].[Backup_Paths].[Last Executed] = @lvLastExecuted
            WHERE [dbo].[Backup_Paths].[AAA Client ID] = @lvAAAID
            AND [dbo].[Backup_Paths].[Process Name] = @lvName
            AND [dbo].[Backup_Paths].[UNC Path] = @lvPath'
END

SET @lvSQL = @lvSQL + ' WHERE [dbo].[Backup_Paths].SomeProp = 7'

execute sp_executesql @lvSQL, '@lvFunction int,
                               @lvECMID varchar(12),
                               @lvECMID_new varchar(12),
                               @lvName varchar(35),
                               @lvName_new varchar(35),
                               @lvPath varchar(255),
                               @lvPath_new varchar(255),
                               @lvLastExecuted datetime',
                      @lvFunction,
                      @lvECMID,
                      @lvECMID_new,
                      @lvName,
                      @lvName_new,
                      @lvPath,
                      @lvPath_new,
                      @lvLastExecuted;