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? mssqltips.com/sqlservertip/1145/… — wg1k
– wg1k, Commented Feb 27, 2020 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
– Trevor, Commented Feb 27, 2020 at 18:24
@Çöđě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
– Willy, Commented Feb 27, 2020 at 19:04

Thom A · Accepted Answer · 2020-02-27 18:37:00Z

2

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;

answered Feb 27, 2020 at 18:37

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Trevor Over a year ago

QUOTENAME what's the reasoning behind this, TBH, I've not seen this done before.

Thom A Over a year ago

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. :)

Trevor Over a year ago

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.

Thom A Over a year ago

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 >_< ).

Willy Over a year ago

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!

Luuk · Accepted Answer · 2020-02-27 18:34:04Z

-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, 2020 at 18:34

answered Feb 27, 2020 at 18:30

Luuk

15.4k5 gold badges28 silver badges44 bronze badges

4 Comments

Trevor Over a year ago

This isn't an answer, well it is, but not what the OP needs and isn't helpful IMHO.

Luuk Over a year ago

@Çöđěxěŕ: i tried to answer: "So how can I concatenate a datetime field in T-SQL to a string?"

Trevor Over a year ago

Explain then, how is this concatenating a DateTime to/within a string; it's dynamic.

Luuk Over a year ago

It is not dynamic is a an input parameter named @DateTimeField, pretty fixed....

Collectives™ on Stack Overflow

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

2 Answers 2

5 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related