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

Question

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.?

Answer 1

"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