Inserting DateTime into datetime field TSQL

Question

I'm trying to call a stored procedure in SQL Server 2012 Express from a C# program, to insert a DateTime data type into a column (which is also datetime).

For some reason, I keep getting errors about "Conversation failed when converting date and/or time from character string."

I've checked the culture settings in my SQL server, which is set to us_english, but the datetime format is the ISO standard.

Here's the code from the stored procedure. The values are exactly how they're passed by the C# app.

USE testdb;
DECLARE @Name NVARCHAR(50) = 'Somename', 
    @Location NVARCHAR(50) = 'somelocation',
    @Date DateTime = '2013-10-11 11:00:05.000'

BEGIN

SET NOCOUNT ON;

SELECT @Name, @Location, @Date

if exists (select name from ComputerHistory where name = @Name)
    begin
        DECLARE @Query1 NVARCHAR(MAX)
        if @Location <> 'disconnected'
            begin
                set @Query1 = '
                    update ComputerHistory 
                    set ' + @Location + ' = 1 + ISNULL((select MAX(' + @Location + ') from ComputerHistory where name = ''' + @Name + '''),0),
                    lastdateonline = ''' + @Date + '''
                    where name = ''' + @Name + '''
                    '

                --EXEC sp_executesql @Query1
            end
        else
            begin
                set @Query1 = '
                    update ComputerHistory 
                    set ' + @Location + ' = 1 + ISNULL((select MAX(' + @Location + ') from ComputerHistory where name = ''' + @Name + '''),0)
                    where name = ''' + @Name + '''
                    '
                EXEC sp_executesql @Query1
            end
    end
else
    begin
        DECLARE @Query2 NVARCHAR(150)
        set @Query2 = 'insert into ComputerHistory(name, ' + @Location + ') VALUES(''' + @Name + ''', ''1'')'
        EXEC sp_executesql @Query2
    end
END

score 1 · Answer 1 · answered Oct 17 '13 at 12:29

1

Try cast(@Date as nvarchar(50))

                set @Query1 = '
                update ComputerHistory 
                set ' + @Location + ' = 1 + ISNULL((select MAX(' + @Location + ') from ComputerHistory where name = ''' + @Name + '''),0),
                lastdateonline = ''' + cast(@Date as nvarchar(50)) + '''
                where name = ''' + @Name + '''
                '

answered Oct 17 '13 at 12:29

Andrew

5,215
1
23
42

That should work. It looks like SQL Server doesn't apply the correct type in the implicit cast. The code `declare @date datetime = '2013-10-11 11:00:05.000'; select 'Date is: ' + @date` doesn't work while `declare @date datetime = '2013-10-11 11:00:05.000'; select 'Date is: ' + convert(nchar(25), @date)` works. – Ricardo Oct 17 '13 at 12:38
2

This code is building a string (for dynamic SQL). Whenever sql combines different data types, it uses data type precedence to do so. Documentation here: http://technet.microsoft.com/en-us/library/ms190309.aspx In this case, there is a string and a datetime. SQL tries to convert the string to the datetime instead of the other way around. – George Mastros Oct 17 '13 at 12:57

Inserting DateTime into datetime field TSQL

1 Answers1