SQL Server : error converting data type nvarchar to datetime

Question

EDIT Thank you for all your help.

First thing, I have read tons of post and question of people having this same error, mines varies for int and bigint but the error stays the same, I can not insert or update. I am working with Microsoft SQL Server 2017 Management Studio and I've having this problem for the last two to three days, I know there are tons of answers I just don't know the F is wrong.

This is my stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[myproc]
    (@name VARCHAR(50),
     @ID BIGINT,
     @birthdate DATETIME,
     @Enabled BIT,
     @married BIT,
     @employees INT,
     @hiredDate DATETIME)
AS
BEGIN 
    SET NOCOUNT ON

    DECLARE @sql AS NVARCHAR(MAX)       
    SET @sql ='IF EXISTS(SELECT * FROM MyTable WHERE MyTable.ID ='+@ID+')
                     UPDATE MyTable
                     SET
                     name ='+@name+'
                    ,birthdate='+@birthdate+'
                    ,Enabled ='+@Enabled+'
                    ,married='+@married+'
                    ,employees='+@employees+'
                    ,hiredDate='+@hiredDate+'
                    where ID = '+@ID+'
                ELSE
                INSERT INTO MyTable(name,ID,birthdate,Enabled,married,employees,hiredDate)VALUES(
                '+@name+','+@ID+','+convert(varchar,@birthdate,20)+','+@Enabled+','+@married+','+@employees+','+convert(varchar,@hiredDate,20)+')'

    EXEC (@SQL)
END

I am sick of this damn error and I am losing it.

Msg 8114, Level 16, State 5, Procedure testinsert, Line 0 [Batch Start Line 0]
Error converting data type varchar to datetime.

I made this little table just so i could ask and try to make this trial

I run this stored procedure with:

DECLARE @return_value INT

EXEC @return_value = testinsert
        @name = 'asd',
        @ID = 25,
        @birthdate = '2018-12-10 16 45 00',
        @Enabled = 1,
        @married = NULL,
        @employees = NULL,
        @hiredDate = NULL

SELECT  'Return Value' = @return_value
GO

Table:

[dbo].[MYDTABLE]
           ([name]
           ,[ID]
           ,[birthdate]
           ,[Enabled]
           ,[married]
           ,[employees]
           ,[hiredDate])
     VALUES
           (<name, varchar(50),>
           ,<ID, bigint,>
           ,<birthdate, datetime,>
           ,<Enabled, bit,>
           ,<married, bit,>
           ,<employees, int,>
           ,<hiredDate, datetime,>)
GO

Use sp_executesql and pass the values in as parameters. Then you won't have type problems with dynamic SQL. It is unclear why you are using dynamic SQL here in the first place. This looks like regular SQL -- in fact, regular SQL that might be replaced with a single MERGE statement. — Gordon Linoff
– Gordon Linoff, Commented Aug 31, 2018 at 19:53
Why are you using dynamic sql here? There is absolutely no need it from what you posted. And your code is wide open to sql injection because you just build up a string and execute it. That defeats the advantage of passing in parameters in the first place. — Sean Lange
– Sean Lange, Commented Aug 31, 2018 at 19:57
@GordonLinoff I want to enter the table as a parameter, but if this does not work, how can i expect it to work with a variable? and I tried the implicit way and i get what I think is the same error but the implicit version of it. — Zealot91
– Zealot91, Commented Aug 31, 2018 at 19:58
@Zealot91 . . . If the table needs to be a parameter, then munge the query string with the table name, but pass everything else in as a parameter. — Gordon Linoff
– Gordon Linoff, Commented Aug 31, 2018 at 20:01
And you can still use parameters even with dynamic sql if you use sp_executesql, just not the table name. You would need to wrap that in QUOTENAME though to help mitigate sql injection. — Sean Lange
– Sean Lange, Commented Aug 31, 2018 at 20:02

Sean Lange · Accepted Answer · 2018-08-31 20:49:52Z

4

Since you are trying to pass in table name (not in your question but mentioned in the comments) you do need dynamic sql because table names cannot be parameterized. However, you can pass in the name as a parameter to your procedure just like all the values. Then you can parameterize your dynamic sql which will eliminate all the hassle of datatype conversions and such. Something like this should be pretty close. I also added a lot of white space so this is easier to read.

ALTER PROCEDURE [dbo].[myproc]
(    
    @name varchar(50)
    , @ID bigint
    , @birthdate datetime
    , @Enabled bit
    , @married bit 
    , @employees int
    , @hiredDate datetime
    , @TableName sysname
)
AS
BEGIN 
    SET NOCOUNT ON
    DECLARE @sql AS NVARCHAR(MAX)       
    SET @sql = 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@TableName) + ' WHERE ID = @ID )
                     UPDATE ' + QUOTENAME(@TableName) + ' 
                     SET
                     name = @name
                    , birthdate = @birthdate
                    , Enabled = @Enabled
                    , married = @married
                    , employees = @employees
                    , hiredDate = @hiredDate
                    where ID = @ID
                ELSE
                INSERT INTO ' + QUOTENAME(@TableName) + '
                (
                    name
                    , ID
                    , birthdate
                    , Enabled
                    , married
                    , employees
                    , hiredDate
                )VALUES
                (
                    @name
                    , @ID
                    , @birthdate
                    , @Enabled
                    , @married
                    , @employees
                    , @hiredDate
                )'
EXEC sp_executesql @sql,
    N'@name varchar(50)
    , @ID bigint
    , @birthdate datetime
    , @Enabled bit
    , @married bit 
    , @employees int
    , @hiredDate datetime'
    , @name = @name 
    , @ID = @ID
    , @birthdate = @birthdate
    , @Enabled = @Enabled
    , @married = @married
    , @employees = @employees
    , @hiredDate = @hiredDate

END

edited Aug 31, 2018 at 20:49

answered Aug 31, 2018 at 20:18

Sean Lange

33.7k3 gold badges28 silver badges42 bronze badges

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

11 Comments

Lukasz Szozda Over a year ago

Yes, this will work. But I see antipattern here. Each table will have same structure so it looks like table per date/customer/....

Sean Lange Over a year ago

@LukaszSzozda what do you mean that it is a table per date/customer? This seems like what the OP has. Not a good way to model data for sure.

Zealot91 Over a year ago

Sorry but I still get the same error I have been dealing with so far: ` Msg 8114, Level 16, State 5, Procedure dbo.myproc, Line 0 [Batch Start Line 0] Error converting data type nvarchar to datetime.`

Lukasz Szozda Over a year ago

@SeanLange It is common scenario SELECT * FROM sales + @yymm where you have the same structure over multiple tables. I provided table per date as an example. Nothing specific with OPs scenario.

Sean Lange Over a year ago

If there are table that have the date in the name that is the problem. And yes that is an anti-pattern for sure. Anytime you find yourself needing to pass in the table name to run the same query it is a clear sign the design phase when wrong.

|

Community · Accepted Answer · 2020-06-20 09:12:55Z

3

There is no need for dynamic SQL at all:

ALTER PROCEDURE [dbo].[myproc]
(    
    @name varchar(50),@ID bigint,@birthdate datetime,@Enabled bit
    ,@married bit ,@employees int,@hiredDate datetime
)
AS
BEGIN 
SET NOCOUNT ON
IF EXISTS(SELECT * FROM MyTable WHERE MyTable.ID =@id)
     UPDATE MyTable
     SET name =name
        ,birthdate=@birthdate
        ,Enabled =@Enabled
        ,married=@married
        ,employees=@employees
        ,hiredDate=@hiredDate
      where ID = @ID;
 ELSE
INSERT INTO MyTable(name,ID,birthdate,Enabled,married,employees,hiredDate)
VALUES(@name, @ID, @birthdate, @Enabled, @married, @employees, @hiredDate) ;      
END

EDIT:

I run this sp with:

    @birthdate = '2018-12-10 16 45 00',

I propose to provide parameter with culture independent format ISO-8601 YYYY-MM-DDThh:mm:ss

 @birthdate = '2018-12-10T16:45:00',

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Aug 31, 2018 at 19:54

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

5 Comments

Zealot91 Over a year ago

i want to give the table as a parameter, but if this does not work, how will i make it work with the parameter?

Lukasz Szozda Over a year ago

@Zealot91 Then make dynamic query and concatenate table_name after sanitation with QUOTENAME but provide rest of parameters using sp_executesql

Zealot91 Over a year ago

I tried the implicit way and got Implicit conversion from data type datetime to bigint is not allowed. Use the CONVERT function to run this query. that is the same error in a way.

Zealot91 Over a year ago

Also, i get the same error with your answer: Msg 8114, Level 16, State 5, Procedure testinsert, Line 0 [Batch Start Line 0] Error converting data type varchar to datetime.

Lukasz Szozda Over a year ago

@Zealot91 How do you call stored procedure? Please pass EXEC [dbo].[myproc] ..., @birthdate = '20180101', ... With culture independent date literal.

marc_s · Accepted Answer · 2018-08-31 21:28:23Z

2

The problem was because you didn't convert the data, your fixed script below:

ALTER PROCEDURE [dbo].[myproc]
    (@name VARCHAR(50),
     @ID BIGINT,
     @birthdate DATETIME,
     @Enabled BIT,
     @married BIT,
     @employees INT,
     @hiredDate DATETIME)
AS
AS
BEGIN 
    SET NOCOUNT ON
    DECLARE @sql AS NVARCHAR(MAX)       
    SET @sql ='IF EXISTS(SELECT * FROM MyTable WHERE MyTable.ID ='+CONVERT(VARCHAR,@ID)+')
                     UPDATE MyTable
                     SET
                     name ='''+@name+'''
                    ,birthdate=CONVERT(DATETIME,'''+CONVERT(VARCHAR,@birthdate,20)+''',20)
                    ,Enabled ='+CONVERT(VARCHAR,@Enabled)+'
                    ,married='+CONVERT(VARCHAR,@married)+'
                    ,employees='+CONVERT(VARCHAR,@employees)+'
                    ,hiredDate=CONVERT(DATETIME,'''+CONVERT(VARCHAR,@hiredDate,20)+''',20)
                    where ID = '+CONVERT(VARCHAR,@ID)+'
                ELSE
                INSERT INTO MyTable(name,ID,birthdate,Enabled,married,employees,hiredDate)VALUES(
                '''+@name+''','+CONVERT(VARCHAR,@ID)+',CONVERT(DATETIME,'''+convert(varchar,@birthdate,20)+''',20),'+CONVERT(VARCHAR,@Enabled)+','+CONVERT(VARCHAR,@married)+','+CONVERT(VARCHAR,@employees)+',CONVERT(DATETIME,'''+convert(varchar,@hiredDate,20)+''',20))'
PRINT (@SQL)

END
GO

edited Aug 31, 2018 at 21:28

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Aug 31, 2018 at 20:24

Isaac Caires Santana

213 bronze badges

3 Comments

Zealot91 Over a year ago

I still get the same error I dont know if I am converting wrong or what.: Msg 8114, Level 16, State 5, Procedure dbo.myproc, Line 0 [Batch Start Line 0] Error converting data type nvarchar to datetime.

Isaac Caires Santana Over a year ago

I've created a table based in the parameters of your procedure and it worked here, how did you create your table ?

Zealot91 Over a year ago

I shared the table in the question :)

Collectives™ on Stack Overflow

SQL Server : error converting data type nvarchar to datetime

3 Answers 3

11 Comments

5 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

11 Comments

5 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related