Conversion of C# datetime to sql server datetime is throwing an error

Question

In C# a DateTime property with value {27-01-2017 12.00.00 AM} is being passed in a data table to a procedure with an UTT parameter. UTT also has the same datatype datetime. I am using the generic method provided below. I cannot explicitly convert data type.

Error : The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value. The data for table-valued parameter @UttParameter doesn't conform to the table type of the parameter.
SQL Server error is: 242, state: 3
The statement has been terminated.

public static DataTable ToDataTable<T>(IList<T> items, bool usePropertyMappingName = false)
    {
        DataTable dataTable = null;

        if (items != null)
        {
            using (dataTable = new DataTable(typeof(T).Name))
            {
                dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;

                // Get all the properties.
                PropertyInfo[] props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);

                foreach (PropertyInfo prop in props)
                {
                    string columnName = prop.Name;

                    if (usePropertyMappingName)
                    {
                        var mappingAttribute = prop.GetCustomAttributes(typeof(PropertyMappingAttribute), true).FirstOrDefault() as PropertyMappingAttribute;

                        if (mappingAttribute != null && !string.IsNullOrEmpty(mappingAttribute.Name))
                        {
                            columnName = mappingAttribute.Name;
                        }
                    }

                    // Setting column names as Property names.
                    dataTable.Columns.Add(columnName, prop.PropertyType);
                }

                foreach (T item in items)
                {
                    var values = new object[props.Length];
                    for (int i = 0; i < props.Length; i++)
                    {
                        // Inserting property values to data table rows.
                        values[i] = props[i].GetValue(item, null);
                    }

                    dataTable.Rows.Add(values);
                }
            }
        }

        return dataTable;
    }

You're not passing it as a **datetime**, you're passing it as a **string**. You need to post the code that configures the parameter values in C#, including that parameter and its type. If you had passed it as a **datetime** then there would be no conversion so the very fact that you have a conversion error means you're not really doing what you think or say you're doing. — Lasse V. Karlsen, Jan 27 '17 at 07:14
Are the region settings same in SQL server and your server/machine? — Vijay, Jan 27 '17 at 07:16
@Lasse V. Karlsen, There is a List, Class1 has many properties among which datetime is one of the property. There is a generic method which converts a list of items into a data table. There is no explicit data type conversion happening. — Anoop H.N, Jan 27 '17 at 07:26
*There is a generic method which converts a list of items into a data table*: Show the code, at least the part dealing with datetime values... — Shnugo, Jan 27 '17 at 07:40
@Shnugo , I have now provided generic code which converts the list into datatable. I cannot modify dataTable.Columns.Add(prop.Name); to dataTable.Columns.Add(prop.Name, prop.PropertyType); Because it's a generic method. Nullable properties can not be set. {"DataSet does not support System.Nullable<>."} — Anoop H.N, Jan 27 '17 at 07:47
This `{27-01-2017 12.00.00 AM}` is a rather odd format. If you are trying to write an [ODBC timestamp](http://stackoverflow.com/questions/15759458/what-is-ts-2013-04-02-000000), the format would be `{ts '2017-01-27 00:00:00'}` Where did you get that string from? Can you post the c# code that sends it to the database? — John Wu, Jan 27 '17 at 07:58
C# code has a list of properties among which has DateTime is the one. While converting the list to data table, it is implicitly converting DateTime to String. — Anoop H.N, Jan 27 '17 at 08:02

score 1 · Answer 1 · answered Jan 27 '17 at 07:22

1

Remove the quotation marks

"@UttParameter"

@UttParameter

answered Jan 27 '17 at 07:22

Ben

514
2
10

This is not at all a problem. Actual problem is with the datatype which is being implicitly converted from datetime to string. – Anoop H.N Jan 27 '17 at 07:58

score 1 · Answer 2 · answered Jan 27 '17 at 08:10

1

You are using InvariantCulture as DataTable locale. Invariant culture expects Date to be in yyyy-MM-dd format.

answered Jan 27 '17 at 08:10

Vijay

523
4
13

The problem was with the type conversion. I had to specify the type as `DateTime` while creating the data table. Now the generic method has been modified to handle nullable datatypes. As in the comment you asked, I thought you were asking about timezone of database server and application server. Later I noticed that someone had changed the date format of database server. – Anoop H.N Jan 27 '17 at 11:21

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

Your code - as it is now - will transfer any value on string level. This is a really bad approach. The implicit conversions taking place are highly depending on your system's settings (language and culture). The worst part is: This might work all great on your machine while you are testing it, but on a customer's system it breaks with strange messages. Happy Debugging :-(

Change your code like this

foreach (PropertyInfo prop in props) {
    // Setting column names as Property names.
    if (prop.PropertyType.IsGenericType && prop.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
        dataTable.Columns.Add(prop.Name, prop.PropertyType.GetGenericArguments()[0]);
    else
        dataTable.Columns.Add(prop.Name, prop.PropertyType);
}

This will add the column - even if this is a nullable type - with the correct data type.

credits: This answer helped me

UPDATE Even simpler

(thx to Yves M. in a comment below the linked answer)

foreach (PropertyInfo prop in props) {
    // Setting column names as Property names.
        dataTable.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
}

dataTable.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType); This worked perfectly. Thanks! — Anoop H.N, Jan 27 '17 at 09:38

Conversion of C# datetime to sql server datetime is throwing an error

3 Answers3

UPDATE Even simpler

Linked