varchar... out-of-range-value

[waynecod]

Hi,
I have the following problem:
I’m developing with Visual Studio 2008, C# in an English environment.
I’m connecting to MS SQL Server 2008 and using the server Management Studio also in English.
I am working on a joint project which has been written with the same software but in a German environment!
The problem I have is when accessing the database I get the following error:

‘The conversion of a varchar data to a datetime data type results in an out-of-range value’

This does work within the German environment.

The problem is with the Dateformat when writing to the database. It can be made to work by adding the following statement at the start of the INSERT query: ‘set dateformat dmy;’.

Visual Studio uses the following dateTime definition:
‘public DateTime(int year, int month, int day, int hour, int minute, int second);’


My question is, is there a setting either in Visual studio or on the database that deals with the dateformats so that I do not have to change the code? If I change the code so it will run on my machine will it run on the other (German) machine?

Thankyou

c-code_it

[Ralf Jansen]

This typically happens when you're relying on automatic string to datetime conversation. The only reliable way of handling this is to use parametrized queries. Everything else will fix it only temporarily. As soon as the environment chances chances are high that your system breaks.

PS. Please don't crosspost. Or at least mention the other sources you are using so whe are not going to spent time on a problem that's already solved for you somewhere else.

[gfoidl]

Hi,

Edit: After reading Ralf's answer mine is more a hack than a serious solution

have a look at System.Globalization.CultureInfo.

You can use the German-culture in c# for parsing the string holding the datetime. Have a look at the example:
[highlight=c#]
using System;
using System.Globalization;
using System.Threading;

namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
// Just for demonstration the culture ist set to en-us:
Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;

// A date-string in German notation:
string s = "20.09.2009";

// Parse the string and return a datetime in respect of the
// culture of the string:
IFormatProvider formatProvider = new CultureInfo("de-AT");
DateTime dt = DateTime.Parse(s, formatProvider);
}
}
}
[/highlight]

Kind regards,
Günther


PS: Die Frage kann hier auch in deutsch gestellt werden.

[Christian Marquardt]

PS: Die Frage kann hier auch in deutsch gestellt werden.

In anbetracht, dass es genügend engl.sprachige Foren gibt und beim suchen im Froum hier gewöhnlich deutsch genutzt wird, ist das mehr als angesagt

[waynecod]

Thank you,
it looks like I will try parametrization of the queries first.
As of the ENGLISH, we are not all so bilingual and I do want to be correctly understood.

waynecod