DateTime

I have an application that is sending the date with the following format dd/
mm/yyyy the field in the table in sql is a datetime and the record is not be
ing inserted. If I manually change the value in the query analyzer to mm/dd
/yyyy the record is inserte
d successfully. How can I change the data type to accept the values as is
ThanksHave you check what the dateformat for the language you are using. You can
do this by issuing the following command:
sp_helplanguage @.@.language
If you want a different format then your langauge format you can change it
by using the SET DATEFORMAT.
Here is an example of where I used two different language. In these
examples us_english like mm/dd/yyyy format and British like dd/mm/yyyy.
Hopefully this will give you some ideas on how to fix you problem.
set language us_english
exec sp_helplanguage @.@.language
create table x(d datetime)
insert into x values ('30/06/2002')
insert into x values ('09/30/2002')
select * from x
drop table x
set language British
exec sp_helplanguage @.@.language
create table x(d datetime)
insert into x values ('30/06/2002')
insert into x values ('09/30/2002')
select * from x
drop table x
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:164CBB03-BCA1-41DB-96A9-6739B33547F7@.microsoft.com...
> I have an application that is sending the date with the following format
dd/mm/yyyy the field in the table in sql is a datetime and the record is not
being inserted. If I manually change the value in the query analyzer to
mm/dd/yyyy the record is inserted successfully. How can I change the data
type to accept the values as is
> Thanks|||does the set language command do that at the server level or database level
or table level or field level?
can I change the datetime field in my db table to accept european time?
"Gregory A. Larsen" wrote:

> Have you check what the dateformat for the language you are using. You ca
n
> do this by issuing the following command:
> sp_helplanguage @.@.language
> If you want a different format then your langauge format you can change it
> by using the SET DATEFORMAT.
> Here is an example of where I used two different language. In these
> examples us_english like mm/dd/yyyy format and British like dd/mm/yyyy.
> Hopefully this will give you some ideas on how to fix you problem.
> set language us_english
> exec sp_helplanguage @.@.language
> create table x(d datetime)
> insert into x values ('30/06/2002')
> insert into x values ('09/30/2002')
> select * from x
> drop table x
> set language British
> exec sp_helplanguage @.@.language
> create table x(d datetime)
> insert into x values ('30/06/2002')
> insert into x values ('09/30/2002')
> select * from x
> drop table x
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Niles" <Niles@.discussions.microsoft.com> wrote in message
> news:164CBB03-BCA1-41DB-96A9-6739B33547F7@.microsoft.com...
> dd/mm/yyyy the field in the table in sql is a datetime and the record is n
ot
> being inserted. If I manually change the value in the query analyzer to
> mm/dd/yyyy the record is inserted successfully. How can I change the data
> type to accept the values as is
>
>|||I'm not sure what you are trying to do, but I don't think you want to mess
with your language setting. The "set language" command is only in affect
for the session. What I really think you need is to use the "set
dateformat" statement to control the format of your input data. Sorry for
the confusion. Something like this:
create table x(d datetime)
set dateformat dmy
insert into x values ('30/06/2002')
set dateformat mdy
insert into x values ('09/30/2002')
select * from x
drop table x
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:5F4A1DCC-329E-4232-8661-26212099F305@.microsoft.com...
> does the set language command do that at the server level or database
level or table level or field level?
> can I change the datetime field in my db table to accept european time?
> "Gregory A. Larsen" wrote:
>
can[vbcol=seagreen]
it[vbcol=seagreen]
> ----
--
> ----
--[vbcol=seagreen]
format[vbcol=seagreen]
not[vbcol=seagreen]
data[vbcol=seagreen]|||Well, this is a vendor product. you're right, I don't want to change the la
nguage The tables are already created and i can't make any changes to the fr
ont end. It is a european vendor. The system came with an MSDE database.
I am trying to switch it to
an enterprise version of sql 2000. When I made the switch, data wasn't bein
g written to the main table. After running a trace, I noticed that the inse
rt to that table was failing because the datetime that was being inserted wa
s in European format and t
he datetime field was rejecting that. Changing the data type to varchar all
owed the insert but I don't want to keep it as varchar obviously. Can this
issue be fixed with some change to the Datetime field (like some formula or
something), any other sugge
stions? I am not sure where to use set dateformat
thanks
"Gregory A. Larsen" wrote:

> I'm not sure what you are trying to do, but I don't think you want to mess
> with your language setting. The "set language" command is only in affect
> for the session. What I really think you need is to use the "set
> dateformat" statement to control the format of your input data. Sorry fo
r
> the confusion. Something like this:
>
> create table x(d datetime)
> set dateformat dmy
> insert into x values ('30/06/2002')
> set dateformat mdy
> insert into x values ('09/30/2002')
> select * from x
> drop table x
>
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Niles" <Niles@.discussions.microsoft.com> wrote in message
> news:5F4A1DCC-329E-4232-8661-26212099F305@.microsoft.com...
> level or table level or field level?
> can
> it
> --
> --
> format
> not
> data
>
>|||http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:ABC7EAEA-A16B-42D9-84B7-A1B4FE9710DB@.microsoft.com...
> Well, this is a vendor product. you're right, I don't want to change the language
The tables are
already created and i can't make any changes to the front end. It is a euro
pean vendor. The system
came with an MSDE database. I am trying to switch it to an enterprise versi
on of sql 2000. When I
made the switch, data wasn't being written to the main table. After running
a trace, I noticed that
the insert to that table was failing because the datetime that was being ins
erted was in European
format and the datetime field was rejecting that. Changing the data type to
varchar allowed the
insert but I don't want to keep it as varchar obviously. Can this issue be
fixed with some change
to the Datetime field (like some formula or something), any other suggestion
s? I am not sure where
to use set dateformat[vbcol=seagreen]
> thanks
>
> "Gregory A. Larsen" wrote:
>|||> Well, this is a vendor product.
You should give constructive feedback to the vendor that they are idiots for
relying on regional date formats like d/m/y or m/d/y.
http://www.aspfaq.com/
(Reverse address to reply.)