How to convert a datetime string to datetime in SQL Server

Question

From within my C# app I'm calling a stored procedure with a TVP. A couple of columns are datetime. A call to the SP might look like:

declare @p1 dbo.MyTvp
insert into @p1 values('2020-03-19 00:00:01','2020-03-30 23:59:59')
exec MySp @criteria=@p1

The above code is automatically generated in C#. In the SP, the part handling the dates is:

declare @datefrom datetime;
---
SET @sql = CONCAT(@sql, ' AND date_from >= ''', @datefrom, '''');

SQL Server locale is German.

The above throws an error due to conversion from varchar to datetime. However, if the datetime values that I pass are formatted as follows:

declare @p1 dbo.MyTvp
insert into @p1 values('19.03.2020 00:00:01','30.03.2020 23:59:59')
exec MySp @criteria=@p1

The SP works fine.

The class used as a source is:

public class MyCriteria
{
    public DateTime DateFrom { get; set; }
}

And the table type is:

CREATE TYPE [dbo].[MyTvp] AS TABLE(
    [DateFrom] [datetime] NULL
)

I convert an instance of MyCriteria into a DataTable using an extension method, and then use Dapper to execute the SP:

var criteria = new List<MyCriteria>() { myCriteria }.ToDataTable();
return await conn.QueryAsync<SomeResult>(new CommandDefinition("MySp", new { criteria }, commandType: CommandType.StoredProcedure, cancellationToken: ct));

What I don't understand is at what stage does the conversion from datetime to varchar or DateTime to string occurs.

So how exactly do I need to convert the dates to get the SP to work? Should I do the conversion at the DB level or in my C# app?

EDIT

This is the extension method used to convert a class to a datatable so that it can be passed on as a TVP to the SP:

    public static DataTable ToDataTable<T>(this IEnumerable<T> items)
    {
        var dataTable = new DataTable(typeof(T).Name);

        //Get all the properties not marked with Ignore attribute
        var properties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance)
                                  .Where(x => x.GetCustomAttributes(typeof(XmlIgnoreAttribute), false).Length == 0).ToList();

        //Set column names as property names
        foreach (var property in properties)
        {
            if (!property.PropertyType.IsEnum && !property.PropertyType.IsNullableEnum())
            {
                var type = property.PropertyType;

                //Check if type is Nullable like int?
                if (Nullable.GetUnderlyingType(type) != null) 
                    type = Nullable.GetUnderlyingType(type);

                dataTable.Columns.Add(property.Name, type);
            }
            else dataTable.Columns.Add(property.Name, typeof(int));
        }

        //Insert property values to datatable rows
        foreach (T item in items)
        {
            var values = new object[properties.Count];
            for (int i = 0; i < properties.Count; i++)
            {
                values[i] = properties[i].GetValue(item, null);
            }

            dataTable.Rows.Add(values);
        }

        return dataTable;
    }

EDIT 2

The problem is the SQL that is being generated by C#/Dapper which is used to populate the TVP passed to the SP. A simple test can be seen by doing the following:

DECLARE @test TABLE (
    [DateCol] datetime NOT NULL
);

INSERT INTO @test VALUES ('2020-02-19 00:00:01'); --doesnt work
INSERT INTO @test VALUES (CONVERT(datetime, '2020-02-19 00:00:01', 120)); --works

The CONVERT function returns the date in the same format as the first INSERT statement. However the first statement doesn't work.

Answer 1

From discussion in the comments, it sounds like a: the data in the TVP is typed (datetime), and b: there is only one row in this case; that's great - it means we can simplify hugely; what we'd want to do here is pull the values from the TVP into locals, and just work with those. Now, based on @datefrom in the example code, it sounds like you've already done the first step, so all we need to do is fix how the dynamic SQL is composed and executed. In the question we have:

SET @sql = CONCAT(@sql, ' AND date_from >= ''', @datefrom, '''');

which is presumably followed later by:

EXEC (@sql);

---

Instead, we can parameterize the dynamic SQL:

SET @sql = @sql + ' AND date_from >= @datefrom ';

and pass the parameters into our dynamic SQL:

exec sp_executesql @sql, N'@datefrom datetime', @datefrom

The second parameter to sp_executesql gives the definition string for all the actual parameters, which come after it sequentially.

Now the code is entirely safe from SQL injection, and you don't have any string/date conversions to worry about.

Note that the parameter names don't need to be the same in the "inside" and "outside" parts, but they often are (for convenience and maintainability).