|
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 quoteHide 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 quoteHide 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 quoteHide 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 quoteHide 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 quoteHide 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
stop using dynamic sql
how to write extended stored procedure to generate sequences. dll & Web Service in VB.Net 2005 Stored Procedure UDT, what's the point? How to use getdate() and user_name() functions with Windows NT login No transaction is active Cursor doesn't loop properly??? create function Compatibility level, SQL 2005 Number of days in previous quarter? |
|||||||||||||||||||||||
