3

I have a Datatable with two DateTime columns: date_from and date_to.

string dateFrom = row["date_from"].ToString();
string dateTo = row["date_to"].ToString();

When this code is run in a customer environment, it returns dates in the following format:

"2016-01-01 00.00.00"
"2016-02-01 00.00.00"

This string is then inserted into SQL:

SELECT * FROM my_table
WHERE UPPER(status) = 'N'
AND trans_date >= {ts '1900-01-01 00.00.00' }
AND trans_date <= {ts '1900-01-01 00.00.00' }

When this SQL is executed it returns the error "Conversion failed when converting date and/or time from character string."

So a quick fix for this is to run a string.Replace() where I replace period with colon:

dateFrom = dateFrom.Replace(".", ":");

However, my question is rather why the date is returned with periods as the timestamp separator and not colons?

I have created this test on my local machine, but unfortunately for this scenario it returns a correct representation of the DateTime string:

DataTable table = new DataTable();
table.Columns.Add("date", typeof(DateTime));

DataRow row = table.NewRow();
row["date"] = DateTime.Now;
table.Rows.Add(row);

DateTime date = DateTime.Parse(table.Rows[0]["date"].ToString());

My guess so far would be that it has something to do with the customers culture, but I would really appreciate input from someone who has experience with this so I can give feedback to the customer about why this happened in the first place.

It might just be a coincidence, but it is worth mentioning that this only happens for users with Windows 10. Everyone using Windows 7 does not get this error.

soberga
  • 289
  • 7
  • 21
  • 3
    Why are you using string concatenation with SQL, why not use parameters? If you do that then it does not matter what the culture is set to. – Scott Chamberlain Feb 29 '16 at 17:35
  • 1
    You could use a custom date string to avoid local culture problems : https://msdn.microsoft.com/en-us/library/8kb3ddd4(v=vs.110).aspx#timeSeparator e.g date.ToString("yyyy-MM-dd H:mm:ss") – PaulF Feb 29 '16 at 17:41
  • I found this: http://stackoverflow.com/questions/7498058/net-3-5-formats-times-using-dots-instead-of-colons-as-timeseparator-for-it-it – Steve Feb 29 '16 at 17:51
  • Thank you for the input about parameterization and using a custom ToString(). I will implement this. – soberga Feb 29 '16 at 18:39

1 Answers1

2

As you says, this is about customer's culture. Specifically CultureInfo.DateTimeFormat.

This property returns a DateTimeFormatInfo object that defines the culturally appropriate format of displaying dates and times.

This object have a property TimeSeparator that gets or sets the string that separates the components of time, that is, the hour, minutes, and seconds.

You can setup this property to specify a separator like :.

However a better approach is passing a specific culture to ToString() method like CultureInfo.InvariantCulture (culture-independent):

string dateFrom = ((DateTime)row["date_from"]).ToString(CultureInfo.InvariantCulture);
string dateTo = ((DateTime)row["date_to"]).ToString(CultureInfo.InvariantCulture);

or use your own format:

string dateFrom = ((DateTime)row["date_from"]).ToString("yyyy-MM-dd HH:mm:ss");
string dateTo = ((DateTime)row["date_to"]).ToString("yyyy-MM-dd HH:mm:ss");
Arturo Menchaca
  • 15,783
  • 1
  • 29
  • 53