How to prevent out-of-range datetime value error?

I am passing an ad-hoc Insert statement from c# application to the sql server 2000/2005. Sometimes, if machine (where sql server is installed) datetime format is different than what I am passing in, it is throwing an error.

e.g. : In Insert statement I am passing '2010-03-10 00:00:00-05:00' this but machine regional 开发者_运维技巧date setting is different. I am getting this error:-

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Can I pass some generic date format in Insert statement from c# that works perfectly with any machine Regional Date Time settings.?

"yyyymmdd" is safest, the basic ISO-8601 format. "yyyy-mm-dd" has issues as the link below mentions

With times: "yyyymmdd hh:mm:ss"

SQL Server can be slightly odd when dealing with datetimes. It's mostly fixed in SQL Server 2008 with the new date and time formats. The definitive article by Tibor Karaszi

Edit: And another article by Tony Rogerson for the unbelievers

Rather than building your SQL insert statement dynamically as strings, if you use either stored procedures or parameterised queries you will be able to pass the C# datetime value as a datetime object and there will be no format mismatch to cause problems.

[Correction - this only works on SQL 2008, see gbn's answer for a 2000/2005 valid string]
Alternatively, I believe that regardless of the SQL servers regional settings, if you pass your date time as a full ISO 8601 string it will be handled correct.

2010-03-10T14:17Z

Personally, I would recommend parameterised queries or stored procedures though as they solve lots of other problems too.