Culture-conversion in C# with DateTime

Question

I'm having trouble getting my ASPX-App to work on the productive server. As my local environment is german but the server is english and my whole code didn't provide culture-support, I'm currently implementing that throughout the script.

As you might think, as I'm writing this in here, it doesn't quite work the way I want it to.

The App uses a MSSQL-Database for gathering information that is based on dateranges. So, I use the user-selected daterange for calculation using Date BETWEEN x AND y.

The dates are set by the user and parsed in the script to datetime-values to be able to handle them:

_DateRangeStart = DateTime.Parse(DateStart + " 00:00:00", _Culture);
_DateRangeEnd = DateTime.Parse(DateEnd + " 23:59:59", _Culture);

Where _Culture is defined as following:

public CultureInfo _Culture = CultureInfo.GetCultureInfo("de-DE");

and _DateRangeStart (and End) are the user-input from text-fields.

So, as I look into the skript, it shows me that the user input is transformed from 1.11.2009 (which is a correct german formatted date) to 11/1/2009 00:00:00 AM, which should be 1.11.2009 00:00:00 (for _DateRangeStart).

This value cannot be read by the SQL and it gives me an error that converting a char to a valid datetime was not possible.

Why is the conversion not done correctly?

Answer 1

I'm guessing you are calling ToString() on your DateTime variable and it's outputting in the default format. SQL then tries to interpret it in it's default format and fails. I think what you need to do is format the date in an unambiguous format and send it to SQL Server.

E.g.

  string myDate = _DateRangeStart.ToString("yyyy-MM-dd");

Answer 2

Is your sql server set to another culture than de-de ? That would also create this behavior. If your sql server thinks he's an american he wants american formatted dates.