All geek questions in one place

How to prevent an error of a date and time value out of range?

I am passing an ad-hoc Insert declaration from a C # application on SQL Server 2000/2005. Sometimes, if the machine (where the sql server is installed), the date and time format is different from what I pass, it causes an error.

For example: In the Insert statement, I pass '2010-03-10 00: 00: 00-05-05: 00', but setting the regional dates of the machine is different. I get this error: -

Converting a char data type to a datetime data type resulted in a timeless time value.

Can I pass some general date format in the Insert statement from C #, which works fine with any regional setting time.?

+4
source share
2 answers

"yyyymmdd" is the most secure, basic ISO-8601 format. "yyyy-mm-dd" has problems because the link below mentions

Over time: "yyyymmdd hh: mm: ss"

SQL Server can be a little weird when dealing with dates. It is mostly fixed in SQL Server 2008 with new date and time formats. final article by Tibor Karashi

Edit: And another Tony Rogerson article for unbelievers

+5
source

Instead of dynamically creating the SQL insert statement as strings, if you use either stored procedures or parameterized queries, you can pass the C # date and time value as a datetime object and there will be no format mismatch to cause problems.

[Correction - this only works on SQL 2008, see gbn answer for valid 2000/2005 string]
As an alternative, I believe that regardless of the regional settings of SQL servers, if you pass your time as a full ISO 8601 string, it will be processed correctly.

eg.

2010-03-10T14:17Z

Strike>

Personally, I would recommend parameterized queries or stored procedures, although they also solve many other problems.

+2
source

Source: https://habr.com/ru/post/1303728/

More articles:


All Articles