|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Datetime probleminsert into dbo.test (c1, c2) values (1, '22.1.2006 10:10:10') where c1 is an int, and c2 is a datetime field. This command returns an error. Server: Msg 242, Level 16, State 3, Line 1 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. The statement has been terminated. when I change that command like following SET DATEFORMAT dmy insert into dbo.test (c1, c2) values (1, '22.1.2006 10:10:10') it works fine. I want to set server always accepts dates im dmy format. What can I do for this. Thanks in advance Could you instead pass dates in the following format? It always works:
YYYYMMDD HH:MM:SS Show quote "Levent Helvacioglu" <Levent Helvacio***@discussions.microsoft.com> wrote in message news:813C2E24-BF6A-417C-97A6-567004845256@microsoft.com... > an existing application sends server an sql string like > > insert into dbo.test (c1, c2) values (1, '22.1.2006 10:10:10') > > where c1 is an int, and c2 is a datetime field. This command returns an error. > > Server: Msg 242, Level 16, State 3, Line 1 > The conversion of a char data type to a datetime data type resulted in an > out-of-range datetime value. > The statement has been terminated. > > when I change that command like following > > SET DATEFORMAT dmy > insert into dbo.test (c1, c2) values (1, '22.1.2006 10:10:10') > > it works fine. > > I want to set server always accepts dates im dmy format. > What can I do for this. > > Thanks in advance that way requires application change. Actually there is lots of data in dmy
format. When server changed to SQL 2000, application get following error message from server. It was work fine with previous version SQL server, but not SQL 2000 Show quote "Narayana Vyas Kondreddi" wrote: > Could you instead pass dates in the following format? It always works: > > YYYYMMDD HH:MM:SS > > -- > Vyas, MVP (SQL Server) > SQL Server Articles and Code Samples @ http://vyaskn.tripod.com/ > > > "Levent Helvacioglu" <Levent Helvacio***@discussions.microsoft.com> wrote in > message news:813C2E24-BF6A-417C-97A6-567004845256@microsoft.com... > > an existing application sends server an sql string like > > > > insert into dbo.test (c1, c2) values (1, '22.1.2006 10:10:10') > > > > where c1 is an int, and c2 is a datetime field. This command returns an > error. > > > > Server: Msg 242, Level 16, State 3, Line 1 > > The conversion of a char data type to a datetime data type resulted in an > > out-of-range datetime value. > > The statement has been terminated. > > > > when I change that command like following > > > > SET DATEFORMAT dmy > > insert into dbo.test (c1, c2) values (1, '22.1.2006 10:10:10') > > > > it works fine. > > > > I want to set server always accepts dates im dmy format. > > What can I do for this. > > > > Thanks in advance > > > This might shine some light on the problem: http://www.karaszi.com/SQLServer/info_datetime.asp, more
specifically http://www.karaszi.com/SQLServer/info_datetime.asp#DtFormatsInput. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Levent Helvacioglu" <LeventHelvacio***@discussions.microsoft.com> wrote in message news:D5CB6259-F990-49CA-A7E1-FE263CFF1335@microsoft.com... > that way requires application change. Actually there is lots of data in dmy > format. When server changed to SQL 2000, application get following error > message from server. It was work fine with previous version SQL server, but > not SQL 2000 > > "Narayana Vyas Kondreddi" wrote: > >> Could you instead pass dates in the following format? It always works: >> >> YYYYMMDD HH:MM:SS >> >> -- >> Vyas, MVP (SQL Server) >> SQL Server Articles and Code Samples @ http://vyaskn.tripod.com/ >> >> >> "Levent Helvacioglu" <Levent Helvacio***@discussions.microsoft.com> wrote in >> message news:813C2E24-BF6A-417C-97A6-567004845256@microsoft.com... >> > an existing application sends server an sql string like >> > >> > insert into dbo.test (c1, c2) values (1, '22.1.2006 10:10:10') >> > >> > where c1 is an int, and c2 is a datetime field. This command returns an >> error. >> > >> > Server: Msg 242, Level 16, State 3, Line 1 >> > The conversion of a char data type to a datetime data type resulted in an >> > out-of-range datetime value. >> > The statement has been terminated. >> > >> > when I change that command like following >> > >> > SET DATEFORMAT dmy >> > insert into dbo.test (c1, c2) values (1, '22.1.2006 10:10:10') >> > >> > it works fine. >> > >> > I want to set server always accepts dates im dmy format. >> > What can I do for this. >> > >> > Thanks in advance >> >> >> When I set logins default language by enterpirse manager, it runs normal.
Thanks for help :) Show quote "Tibor Karaszi" wrote: > This might shine some light on the problem: http://www.karaszi.com/SQLServer/info_datetime.asp, more > specifically http://www.karaszi.com/SQLServer/info_datetime.asp#DtFormatsInput. > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > > > "Levent Helvacioglu" <LeventHelvacio***@discussions.microsoft.com> wrote in message > news:D5CB6259-F990-49CA-A7E1-FE263CFF1335@microsoft.com... > > that way requires application change. Actually there is lots of data in dmy > > format. When server changed to SQL 2000, application get following error > > message from server. It was work fine with previous version SQL server, but > > not SQL 2000 > > > > "Narayana Vyas Kondreddi" wrote: > > > >> Could you instead pass dates in the following format? It always works: > >> > >> YYYYMMDD HH:MM:SS > >> > >> -- > >> Vyas, MVP (SQL Server) > >> SQL Server Articles and Code Samples @ http://vyaskn.tripod.com/ > >> > >> > >> "Levent Helvacioglu" <Levent Helvacio***@discussions.microsoft.com> wrote in > >> message news:813C2E24-BF6A-417C-97A6-567004845256@microsoft.com... > >> > an existing application sends server an sql string like > >> > > >> > insert into dbo.test (c1, c2) values (1, '22.1.2006 10:10:10') > >> > > >> > where c1 is an int, and c2 is a datetime field. This command returns an > >> error. > >> > > >> > Server: Msg 242, Level 16, State 3, Line 1 > >> > The conversion of a char data type to a datetime data type resulted in an > >> > out-of-range datetime value. > >> > The statement has been terminated. > >> > > >> > when I change that command like following > >> > > >> > SET DATEFORMAT dmy > >> > insert into dbo.test (c1, c2) values (1, '22.1.2006 10:10:10') > >> > > >> > it works fine. > >> > > >> > I want to set server always accepts dates im dmy format. > >> > What can I do for this. > >> > > >> > Thanks in advance > >> > >> > >> > > Create INSTEAD OF trigger on your table and reformat an input in it.
Show quote "Levent Helvacioglu" <Levent Helvacio***@discussions.microsoft.com> wrote in message news:813C2E24-BF6A-417C-97A6-567004845256@microsoft.com... > an existing application sends server an sql string like > > insert into dbo.test (c1, c2) values (1, '22.1.2006 10:10:10') > > where c1 is an int, and c2 is a datetime field. This command returns an > error. > > Server: Msg 242, Level 16, State 3, Line 1 > The conversion of a char data type to a datetime data type resulted in an > out-of-range datetime value. > The statement has been terminated. > > when I change that command like following > > SET DATEFORMAT dmy > insert into dbo.test (c1, c2) values (1, '22.1.2006 10:10:10') > > it works fine. > > I want to set server always accepts dates im dmy format. > What can I do for this. > > Thanks in advance  |
|||||||||||||||||||||||
Other interesting topics