Datetime problem

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 erro
r.
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 advanceCould 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 Helvacioglu@.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
"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 Helvacioglu@.discussions.microsoft.com> wrote
in
> message news:813C2E24-BF6A-417C-97A6-567004845256@.microsoft.com...
> error.
>
>|||This might shine some light on the problem: http://www.karaszi.com/SQLServer/in...
datetime.asp, more
specifically rl]
Tibor Karaszi, SQL Server MVP
[url]http://www.karaszi.com/sqlserver/default.asp" target="_blank">http://www.karaszi.com/SQLServer/in...ver/default.asp
http://www.solidqualitylearning.com/
"Levent Helvacioglu" <LeventHelvacioglu@.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 dm
y
> format. When server changed to SQL 2000, application get following error
> message from server. It was work fine with previous version SQL server, bu
t
> not SQL 2000
> "Narayana Vyas Kondreddi" wrote:
>|||When I set logins default language by enterpirse manager, it runs normal.
Thanks for help :)
"Tibor Karaszi" wrote:

> This might shine some light on the problem: http://www.karaszi.com/SQLServer/in...o_datetime.asp, more
> specifically /url]
> --
> Tibor Karaszi, SQL Server MVP
> [url]http://www.karaszi.com/sqlserver/default.asp" target="_blank">http://www.karaszi.com/SQLServer/in...ver/default.asp
> http://www.solidqualitylearning.com/
>
> "Levent Helvacioglu" <LeventHelvacioglu@.discussions.microsoft.com> wrote i
n message
> news:D5CB6259-F990-49CA-A7E1-FE263CFF1335@.microsoft.com...
>|||Create INSTEAD OF trigger on your table and reformat an input in it.
"Levent Helvacioglu" <Levent Helvacioglu@.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