title: How to concatenate a datetime field with a string in T-SQL
body: <p>I have a dynamic query and I am trying to execute it using sp_executesql. In the dynamic query I has a field which is of type datetime. This field is passed as a parameter from C# .NET to stored procedure in SQL Server. I am calling this sp from C# using typical commands:</p>
  
  <pre><code>    public static object ExecuteProcedure(int userId, DbConnection con, DbTransaction trans, string procedureName, bool withLog, params MyCustomParameter[] parameters)
      {
          if (con.State != ConnectionState.Open)
              con.Open();
  
          bool bMyTransaction = trans == null;
          if (trans == null)
              trans = con.BeginTransaction();
  
          try
          {
              MyLog log = withLog ? MyLog.FromProcedure(trans, procedureName, parameters) : null;
  
              DbCommand command = con.CreateCommand();
              command.CommandType = System.Data.CommandType.StoredProcedure;
              command.CommandText = procedureName;
              command.Transaction = trans;
              command.AddMyCustomParameters(parameters);
  
              int result = command.ExecuteNonQuery();
  
              if (log != null)
                  log.SaveLogData();
  
              if (bMyTransaction == true)
                  trans.Commit();
              return result;
          }
          catch (Exception ex)
          {
              if (bMyTransaction == true)
                  trans.Rollback();
              return null;
          }
      }
  </code></pre>
  
  <p>It throws an exception in the line:</p>
  
  <pre><code>int result = command.ExecuteNonQuery();
  </code></pre>
  
  <p>The error thrown is (translate from spanish):</p>
  
  <blockquote>
    <p>Error converting a string of characters in date and/or time.</p>
  </blockquote>
  
  <p>The stored procedure is something like below:</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[spLogger]   
      @Id varchar(100),
      @Param1 varchar(100),
      @Param2 varchar(15),
      @Param3 int,
      @DateTimeField datetime,
      @Param4 varchar(100),
      @TargetTable tinyint = 0
  AS
  BEGIN
      DECLARE @sqlCommand nvarchar(max)
      DECLARE @tblName nvarchar(100)  
  
      SET @tblName = CASE @TargetTable 
                       WHEN 0 THEN '[dbo].[LogTable_01]'
                       WHEN 1 THEN '[dbo].[LogTable_02]'
                       WHEN 2 THEN '[dbo].[LogTable_03]'
                       ELSE ''
                    END
  
      IF @tblName &lt;&gt; ''
      BEGIN
          SET @sqlCommand =
          'INSERT INTO ' + @tblName +
                     '([Id] ' +
                     ',[Param1] ' +
                     ',[Param2] ' +
                     ',[Param3] ' +
                     ',[MyDateTimeField] ' +
                     ',[Param4]) ' +
               'VALUES' +
                     '(''' + @Id  + ''',''' + @Param1 + ''',''' + @Param2 + ''',' + CAST(@Param3 AS VARCHAR(10)) + ',' + @DateTimeField + ',''' + @Param4 + ''')'
  
          EXECUTE sp_executesql @sqlCommand
      END    
  END
  </code></pre>
  
  <p>The parameter @DateTimeField is passed from C# and it is of type DateTime. The value passed for this parameter is as below: </p>
  
  <blockquote>
    <p>27/02/2020 18:05:05</p>
  </blockquote>
  
  <p>In the table, MyDateTimeField is defined as datetime.</p>
  
  <p>So how can I concatenate a datetime field in T-SQL to a string?</p>
  
  <p>Also as you can see I concatenate an INT type, parameter @Param3, I would like to know as well if I am concatenating ok as well.</p>