Force date format regardless of culture

Question

I have an issue with certain cultures (Spanish in particular), causing the date string, ToShortDateString(), to come in as dd/mm/yyyy

where END_DATE between '" + asOfDate.AddDays(-30).ToShortDateString() + "' and '" +

This is causing SQL sever errors because we get incorrect date literal strings such as this:

where END_DATE between '17/05/2015' and '16/06/2015' 

What is the best way to force the date format to be mm/dd/yyyy in these cases, where I am building a SQL statement?

Answer 1

The answer is that if you are going to use any format then make sure its an ISO-type format such as yyyy-MM-dd.

where END_DATE between '" + asOfDate.AddDays(-30).ToString("yyyy-MM-dd") + "' and

However, the far more important point is use parameterised queries, not string concatenation. When you do this, a date is a date is a date and format is irrelevant.

Answer 2

You could use:

asOfDate.AddDays(-30).ToString("MM/dd/yyyy", System.Globalization.CultureInfo.InvariantCulture)

(note the uppercase MM)