Problems with Dateformat

Hi all
I have 2 MS SQL Servers (one in the English Language version, one in
German). On each server I have the same database (one for testing, one for
work).
When I'm queriing a datatable from vb.net I'm getting problems with a a
datetime parameter:
The English Language Version accepts: yyyy.MM.dd HH:mm
The German Language Version accepts: dd.MM.yyyy HH:mm
How can I change the DateFormat from the German Version to the English
Version, without reinstalling MS-SQL-Server in English and without changing
all my code to the two dateversions?
Any help would be very appreciated
Tom
You might want to have a look at these:
http://www.karaszi.com/SQLServer/info_datetime.asp Guide to Datetimes
http://www.sqlservercentral.com/colu...qldatetime.asp
Datetimes
http://www.murach.com/books/sqls/article.htm Datetime Searching
Andrew J. Kelly SQL MVP
"Tomilee" <Tomilee@.discussions.microsoft.com> wrote in message
news:66A9DE78-136B-488E-A7E1-4C5387D16590@.microsoft.com...
> Hi all
> I have 2 MS SQL Servers (one in the English Language version, one in
> German). On each server I have the same database (one for testing, one for
> work).
> When I'm queriing a datatable from vb.net I'm getting problems with a a
> datetime parameter:
> The English Language Version accepts: yyyy.MM.dd HH:mm
> The German Language Version accepts: dd.MM.yyyy HH:mm
> How can I change the DateFormat from the German Version to the English
> Version, without reinstalling MS-SQL-Server in English and without
> changing
> all my code to the two dateversions?
> Any help would be very appreciated
> Tom
>
|||Thanks Andrew for your links - unfortunately only the second works. I try to
explain my problem a bit more detailed:
I developed a vb.net client running on desktops. This client is running
queries on a MS SQL Server 2000. After starting the client it asks, witch
server the user wants to choose.
I’ve installed MS SQL servers 2000 twice:
One on my desktop in German.
..
One on a server with my client. This is an English version.
When I query from vb.net it works well on the English MS SQL Server 2000
with the following sample:
"select * from session where GuiAktiv > '" & _
Format(Now.AddSeconds(-60), "yyyy.MM.dd HH:mm") & "' order by id desc"
When I query from vb.net on the German MS SQL Server I receive the following
exception:
"Bei der Konvertierung eines char-Datentyps in einen datetime-Datentyp
liegt der datetime-Wert au?erhalb des gültigen Bereiches ?
Or in English (my translation):
During the conversion of a char-datatype in a datetime-datatype, the
datetime-value is outside the allowed values
What is going wrong?
Thanks in advance
Tom
"Andrew J. Kelly" wrote:

> You might want to have a look at these:
> http://www.karaszi.com/SQLServer/info_datetime.asp Guide to Datetimes
> http://www.sqlservercentral.com/colu...qldatetime.asp
> Datetimes
> http://www.murach.com/books/sqls/article.htm Datetime Searching
> --
> Andrew J. Kelly SQL MVP
>
> "Tomilee" <Tomilee@.discussions.microsoft.com> wrote in message
> news:66A9DE78-136B-488E-A7E1-4C5387D16590@.microsoft.com...
>
>
|||Tomilee (Tomilee@.discussions.microsoft.com) writes:
> Thanks Andrew for your links - unfortunately only the second works.
The link http://www.karaszi.com/SQLServer/info_datetime.asp works for me.

> I developed a vb.net client running on desktops. This client is running
> queries on a MS SQL Server 2000. After starting the client it asks, witch
> server the user wants to choose.
> I've installed MS SQL servers 2000 twice:
> One on my desktop in German.
> .
> One on a server with my client. This is an English version.
> When I query from vb.net it works well on the English MS SQL Server 2000
> with the following sample:
> "select * from session where GuiAktiv > '" & _
> Format(Now.AddSeconds(-60), "yyyy.MM.dd HH:mm") & "' order by id desc"
>...
This is where you go wrong. When you build your SQL statements, do always
use parameters, never build complete strings. What you should do is this:
cmd.CommandText = &_
"SELECT * FROM session WHERE GuiAktiv > @.oneminuteago ORDER BY id DESC"
cmd.Parameters.Add("@.oneminuteago", SqlDbType.DateTime)
cmd.Parameters(0).Value = Now.AddSeconds(-60))
In this particular case, this has the effect that the date is passed as a
binary value to SQL Server, and there can never be any misunderstanding
about the format.
The above is even more important if your data comes from user input. If
you build strings, a malicious user could enter data with a single quote
in it which would close the string. Then the user can add a completely
other SQL statement that does evil things. Don't laugh. This is called
SQL injection, and is a commonly used by intruders.
What happens now is that you pass a formatted date to SQL Server. Most
date formats in SQL Server are interpreted according to dateformat and
language settings. YYYY-MM-DD does not work with a German dmy setting.
If you are absolute dead set on using strings, you should use any of the
formats:
YYYYMMDD [HH:MM:SS.mmm]
YYYY-MM-DDTHH:MM:DD[.mmm]
T here stands for itself. Brackets show optional components. These are
the only two format that SQL Server will always understand.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||> What happens now is that you pass a formatted date to SQL Server. Most
> date formats in SQL Server are interpreted according to dateformat and
> language settings. YYYY-MM-DD does not work with a German dmy setting.
> If you are absolute dead set on using strings, you should use any of the
> formats:
> YYYYMMDD [HH:MM:SS.mmm]
> YYYY-MM-DDTHH:MM:DD[.mmm]
> T here stands for itself. Brackets show optional components. These are
> the only two format that SQL Server will always understand.
Only note:
format ISO 8601 YYYY-MM-DDTHH:MM:DD[.mmm] isn't supported on MSSQL7.
|||Thank you for this useful information!
Tom
"RADl0PASlV" wrote:

> Only note:
> format ISO 8601 YYYY-MM-DDTHH:MM:DD[.mmm] isn't supported on MSSQL7.
>
>