4

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

gbn
  • 422,506
  • 82
  • 585
  • 676
Jango
  • 5,375
  • 14
  • 57
  • 63

2 Answers2

5

"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

gbn
  • 422,506
  • 82
  • 585
  • 676
2

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.

E.g.

2010-03-10T14:17Z

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

Community
  • 1
  • 1
Simon P Stevens
  • 27,303
  • 5
  • 81
  • 107
  • @gbn. Which bit, the parameters/stored procs or the ISO 8601 string? – Simon P Stevens Mar 10 '10 at 21:42
  • Your ISO 8601 string. See my answer please about why the yyyy-mm-dd is SQL language setting dependent. Using stored procs/params is better because .net would handle the translation and SQL Server would get a native datetime value – gbn Mar 10 '10 at 21:44
  • @gbn. Really!? Crazy. I'll have to check that out. – Simon P Stevens Mar 10 '10 at 21:49
  • 1
    @gbn, yeah thanks. I have. always good to try out this stuff though. Most of our database at work are 2000/2005 so I'm surprised I've not hit that before. I've removed the relevant bits from my answer. – Simon P Stevens Mar 10 '10 at 21:54
  • it's not obvious, but if you use procs or parametrisation then SQL won't see the string and convert, because it's already datetime – gbn Mar 10 '10 at 21:57