How to concatenate a datetime field with a string in T-SQL

Question

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:

    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;
        }
    }

It throws an exception in the line:

int result = command.ExecuteNonQuery();

The error thrown is (translate from spanish):

Error converting a string of characters in date and/or time.

The stored procedure is something like below:

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 <> ''
    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

The parameter @DateTimeField is passed from C# and it is of type DateTime. The value passed for this parameter is as below:

27/02/2020 18:05:05

In the table, MyDateTimeField is defined as datetime.

So how can I concatenate a datetime field in T-SQL to a string?

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.

Did you try to convert the datetime before, then concat? https://www.mssqltips.com/sqlservertip/1145/date-and-time-conversions-using-sql-server/ — Ictus, Feb 27 '20 at 18:24
You don't concatenate it, you should be passing them in with the `sp_executesql`. If you did it the right way, this wouldn't be an issue. — Trevor, Feb 27 '20 at 18:24
@Çöđěxěŕ could you provide a little sample, please? How to pass to sp_executesql? — Willy, Feb 27 '20 at 18:25
@Ralph [here](https://stackoverflow.com/questions/28481189/exec-sp-executesql-with-multiple-parameters) check this out instead of me spreading this out again here. You need to construct a param (definition) var, pass that with your sql itself and then include your params in order... The execution plan would love you more doing it this way. — Trevor, Feb 27 '20 at 18:28
You need to quote the datetime in the concatenated string. SQL Server will coerce the datetime to a string to form the insert statement. An insert statement for a datetime takes a quoted string. The easiest way to debug this is to print out the string `@sqlcommand` then try it in an interactive session. — iakobski, Feb 27 '20 at 18:30
@Çöđěxěŕ A lot of thanks for the idea. I have done what you say and in the same way Lamu answered below. Now it works!!!!!!!! A lot of Thanks!!!!!!! — Willy, Feb 27 '20 at 19:04
@Ralph welcome, glad Larnu broke it down for you and actually a better example for you. — Trevor, Feb 27 '20 at 19:05

score 2 · Accepted Answer · answered Feb 27 '20 at 18:37

Don't inject the parameters; they are parameters. You need parametrise them, by adding them as parameters to sys.sp_executesql. Then you don't have a problem of concatenateing a datetime, because you actually pass a datetime datatype:

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 @CRLF nchar(2) = NCHAR(13) + NCHAR(10); --This helps with formatting
    DECLARE @tblName sysname; --Changed to proper data type

    SET @tblName = CASE @TargetTable 
                     WHEN 0 THEN N'LogTable_01'
                     WHEN 1 THEN N'LogTable_02'
                     WHEN 2 THEN N'LogTable_03'
                  END;

    IF @tblName IS NOT NULL
    BEGIN
        SET @sqlCommand =N'INSERT INTO dbo.' + QUOTENAME(@tblName) + ' ([Id] ' + @CRLF +
                         N'                                        ,[Param1] ' + @CRLF +
                         N'                                        ,[Param2] ' + @CRLF +
                         N'                                        ,[Param3] ' + @CRLF +
                         N'                                        ,[MyDateTimeField] ' + @CRLF +
                         N'                                        ,[Param4]) ' + @CRLF +
                         N'VALUES (@Id, @Param1, @Param2, @Param3, @DateTimeField, @Param4);'l

        --PRINT @SQL; --your debugging friend.
        EXECUTE sys.sp_executesql @sqlCommand, N'@Id varchar(100),@Param1 varchar(100),@Param2 varchar(15),@Param3 int,@DateTimeField datetime,@Param4 varchar(100)',@Id, @Param1, @Param2, @Param3, @DateTimeField, @Param4;

    END;
END;

`QUOTENAME` what's the reasoning behind this, TBH, I've not seen this done before. — Trevor, Feb 27 '20 at 18:43
[Why QUOTENAME is important](https://www.sqlservercentral.com/articles/why-quotename-is-important) @Çöđěxěŕ . TL;DR It ensures **safe** injection of object names. Surprised you haven't seen another answer of mine that uses it. :) — Thom A, Feb 27 '20 at 18:44
great read and interesting, makes perfect sense. Thanks for the feedback, and yes, I'm surprised I have yet to see this as well; first time. — Trevor, Feb 27 '20 at 18:48
Thanks. I tend to wrap any object needs to be injected in `QUOTENAME`, regardless of if it's coming from a "safe" source of not. It just future proofs any code should (when) goal posts move, and also just keeps things safe and working should you have objects name that contain a `]` (cause people are silly, and do create them >_< ). — Thom A, Feb 27 '20 at 18:54
Thanks Lamu for the great example. You saved my day ;) It works like a charm! Also interesting article about QUOTENAME. I didn't know it. Great! — Willy, Feb 27 '20 at 19:10

Luuk · Answer 2 · 2020-02-27T18:34:04.320

-1

select a.a, FORMAT(a.a,'dd/MM/yyyy h:mm:ss tt') 
from
 (select convert(datetime, '2020-02-27 12:30:00' ) as a) a

output:

2020-02-27 12:30:00.000 27/02/2020 12:30:00 PM

This shows how to convert a datetime to a string, which you can use to concatenate to your @sqlCommand.

edited Feb 27 '20 at 18:34

answered Feb 27 '20 at 18:30

Luuk

12,245
5
22
33

1

This isn't an answer, well it is, but not what the OP needs and isn't helpful IMHO. – Trevor Feb 27 '20 at 18:32
@Çöđěxěŕ: i tried to answer: "So how can I concatenate a datetime field in T-SQL to a string?" – Luuk Feb 27 '20 at 18:34
Explain then, how *is this concatenating* a `DateTime` to/within a string; it's dynamic. – Trevor Feb 27 '20 at 18:35
It is not dynamic is a an input parameter named `@DateTimeField`, pretty fixed.... – Luuk Feb 27 '20 at 20:09

How to concatenate a datetime field with a string in T-SQL

2 Answers2