Error converting varchar to datetime when running stored procedure from code

Question

From within my C# app I'm calling a stored procedure using Dapper:

return conn.Query<MyList>("check_data", new { dataToCheck }, commandType: CommandType.StoredProcedure);

dataToCheck is a DataTable because check_data accepts a TVP. This table type has 2 columns (from_date varchar(20), to_date varchar(20)) and is based on the following class.

public class DataToCheckType
{
    public DateTime? from_date {get; set;}
    public DateTime? to_date {get; set;}
}

Dapper then sends the following to SQL Server:

declare @p1 dbo.CheckDataType
insert into @p1 values('2017-04-19 00:00:00','2017-04-19 00:00:00')

exec check_data @dataToCheck=@p1

The SP itself is simple. It calls a function that builds a SQL statement which is then executed:

DECLARE @sql nvarchar(max) = dbo.GetSql(@dataToCheck);

DECLARE @results TABLE (Id int);
INSERT INTO @results EXECUTE(@sql);

The GetSql function starts like this:

DECLARE @fromDate datetime;
DECLARE @toDate datetime;

SELECT 
    @fromDate = CONVERT(datetime, from_date, 120),
    @toDate = CONVERT(datetime, to_date, 120),
FROM 
    @dataToCheck;

And it continues building the SQL statement as expected. When running the SP from T-SQL everything works fine.

However, when running the same from code I get the following error:

check_data : 241 Conversion failed when converting date and/or time from character string

Why does the SP work in T-SQL but not when calling it from code? What is wrong with the dates?

When you declare a variable include the TYPE. I can't tell from you code what is a Varchar and what is a DataTime. — jdweng, Apr 19 '17 at 11:09
This line, `exec check_data @dataToCheck=@p1`, looks suspicious. Specifically, the equal sign. — Dan Bracuk, Apr 19 '17 at 11:10
@DanBracuk That's what being generated and it works in TSQL. SP's dataToCheck parameter is set to p1 variable. — Ivan-Mark Debono, Apr 19 '17 at 11:11
Does your table have the same columns with the same datatype as of the TableValueType in SQL? Looked at http://stackoverflow.com/questions/16042499/table-value-parameter-with-dapper-stored-procedures ?? — Chetan, Apr 19 '17 at 11:17
@ChetanRanpariya Yes because if this wasn't the case it would not work from T-SQL — Ivan-Mark Debono, Apr 19 '17 at 11:20
Running from T-SQL and from C# code is different thing. In C# you need to create DataTable and add columns to it manually. If you have a mismatch there it will not run successfully. That's where is the possibility of getting conversion error. Can you share the code of how are you creating parameter object `dataToCheck` in C# ? — Chetan, Apr 19 '17 at 11:29
@ChetanRanpariya I added the class to the question. Dapper generates this line `insert into @p1 values('2017-04-19 00:00:00','2017-04-19 00:00:00')` so it's quite happy with the conversion. And the error is converting from string to datetime not the other way round. I use an extension to create a `DataTable` from the object and it's used in many other places with success. — Ivan-Mark Debono, Apr 19 '17 at 11:35
Are you sure that's what Dapper sends to SQL Server? It looks to me like the DataTable has `DateTime`s in it rather than strings so the format will probably not be the one you're expecting. It's hard to tell without seeing how you populate dataToCheck though... — petelids, Apr 19 '17 at 12:23
Why your Table is `(from_date varchar(20), to_date varchar(20))` instead of `(from_date date, to_date date)`? Date formats depends on culture and `DATEFORMAT` option. — Mikhail Lobanov, Apr 19 '17 at 13:57

score 0 · Accepted Answer · answered Apr 20 '17 at 07:17

0

The problem was solved by changing the columns in the table type from varchar(20) to date.

answered Apr 20 '17 at 07:17

Ivan-Mark Debono

15,500
29
132
263

Error converting varchar to datetime when running stored procedure from code

1 Answers1