Error when inserting datetime in german SQL Server 2000

Hello!
I have a program that are using a stored procedure to insert a row in
an SQL Server. During several years this program has worked fine using
an English version of SQL server. Now a customer wants to use a german
SQL server, but it doesn't work. The row is never inserted. I get the
following error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Fehler beim
Konvertieren von Datentyp varchar in datetime.
Now, if I change the language setting to english in the "SQL Server
Login properties for 'sa'" it all works. If I restore the language
setting to german in the "SQL Server Login properties for 'sa'", it
all continues to work until I re-boot the PC. When I have re-booted, I
once gain end up with the error that I have described before.
It seems strange to me that SQL Server doesn't behave in a consistent
way. Sometimes it works with german language settings, sometimes it
doesn't.
Any explanations would be very appriciated.
BR / PatrikThis is because dateformat changes according to language settings.
date format considerations while insert and update:
You can SET DATEFORMAT and modify code as follows.
create table test(dt datetime)
go
declare @.dt varchar(32)
set @.dt ='26/07/2002'
set dateformat dmy --change dateformat
insert into test values(convert(datetime,@.dt)) ;
select * from test;
go
OR other method would be to use explicit coversion of the
date using CONVERT function.
create table test(dt datetime)
go
declare @.dt varchar(32)
set @.dt ='26/07/2002'
insert into test values(convert(datetime,@.dt, 103)) ;
select * from test;
go
--
-Vishal
"Patrik Johansson" <patrikmjohansson@.yahoo.se> wrote in message
news:8d5c526d.0307010514.b59427d@.posting.google.com...
> Hello!
> I have a program that are using a stored procedure to insert a row in
> an SQL Server. During several years this program has worked fine using
> an English version of SQL server. Now a customer wants to use a german
> SQL server, but it doesn't work. The row is never inserted. I get the
> following error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Fehler beim
> Konvertieren von Datentyp varchar in datetime.
> Now, if I change the language setting to english in the "SQL Server
> Login properties for 'sa'" it all works. If I restore the language
> setting to german in the "SQL Server Login properties for 'sa'", it
> all continues to work until I re-boot the PC. When I have re-booted, I
> once gain end up with the error that I have described before.
> It seems strange to me that SQL Server doesn't behave in a consistent
> way. Sometimes it works with german language settings, sometimes it
> doesn't.
> Any explanations would be very appriciated.
> BR / Patrik|||Patrik,
Yes, language and localizations are a problem to deal with. You might find
this topic helpful:
Writing International Transact-SQL Statements in the MSDN at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_14_3unn.asp
You can also manage this by setting the language of the connection that is
transmitting data. For this use: SET LANGUAGE xxx for a connection or use
sp_defaultlanguage to set the default for a login.
Russell Fields
"Patrik Johansson" <patrikmjohansson@.yahoo.se> wrote in message
news:8d5c526d.0307010514.b59427d@.posting.google.com...
> Hello!
> I have a program that are using a stored procedure to insert a row in
> an SQL Server. During several years this program has worked fine using
> an English version of SQL server. Now a customer wants to use a german
> SQL server, but it doesn't work. The row is never inserted. I get the
> following error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Fehler beim
> Konvertieren von Datentyp varchar in datetime.
> Now, if I change the language setting to english in the "SQL Server
> Login properties for 'sa'" it all works. If I restore the language
> setting to german in the "SQL Server Login properties for 'sa'", it
> all continues to work until I re-boot the PC. When I have re-booted, I
> once gain end up with the error that I have described before.
> It seems strange to me that SQL Server doesn't behave in a consistent
> way. Sometimes it works with german language settings, sometimes it
> doesn't.
> Any explanations would be very appriciated.
> BR / Patrik|||Pass the date in YYYYMMDD or YYYY-MM-DD format. Then there is no confusion
for people *or* the software.
"Patrik Johansson" <patrikmjohansson@.yahoo.se> wrote in message
news:8d5c526d.0307010514.b59427d@.posting.google.com...
> Hello!
> I have a program that are using a stored procedure to insert a row in
> an SQL Server. During several years this program has worked fine using
> an English version of SQL server. Now a customer wants to use a german
> SQL server, but it doesn't work. The row is never inserted. I get the
> following error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Fehler beim
> Konvertieren von Datentyp varchar in datetime.
> Now, if I change the language setting to english in the "SQL Server
> Login properties for 'sa'" it all works. If I restore the language
> setting to german in the "SQL Server Login properties for 'sa'", it
> all continues to work until I re-boot the PC. When I have re-booted, I
> once gain end up with the error that I have described before.
> It seems strange to me that SQL Server doesn't behave in a consistent
> way. Sometimes it works with german language settings, sometimes it
> doesn't.
> Any explanations would be very appriciated.
> BR / Patrik|||Only YYYYMMDD without any separators works always:
SET DATEFORMAT dmy
SELECT ISDATE('2003-07-13'), ISDATE('20030713')
SET DATEFORMAT mdy
SELECT ISDATE('2003-07-13'), ISDATE('20030713')
I can see that you as an American are not that used to dealing with date
formats as I as a European am ;-)
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:uame7U9PDHA.2768@.tk2msftngp13.phx.gbl...
> Pass the date in YYYYMMDD or YYYY-MM-DD format. Then there is no
confusion
> for people *or* the software.
>
> "Patrik Johansson" <patrikmjohansson@.yahoo.se> wrote in message
> news:8d5c526d.0307010514.b59427d@.posting.google.com...
> > Hello!
> >
> > I have a program that are using a stored procedure to insert a row in
> > an SQL Server. During several years this program has worked fine using
> > an English version of SQL server. Now a customer wants to use a german
> > SQL server, but it doesn't work. The row is never inserted. I get the
> > following error:
> >
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Fehler beim
> > Konvertieren von Datentyp varchar in datetime.
> >
> > Now, if I change the language setting to english in the "SQL Server
> > Login properties for 'sa'" it all works. If I restore the language
> > setting to german in the "SQL Server Login properties for 'sa'", it
> > all continues to work until I re-boot the PC. When I have re-booted, I
> > once gain end up with the error that I have described before.
> >
> > It seems strange to me that SQL Server doesn't behave in a consistent
> > way. Sometimes it works with german language settings, sometimes it
> > doesn't.
> >
> > Any explanations would be very appriciated.
> >
> > BR / Patrik
>|||> I can see that you as an American are not that used to dealing with date
> formats as I as a European am ;-)
Hey, don't call me an American... I'm a Canuck, just ask Tom...
:-Psql