SQL-ASP.NET datetime format problem ???

16 Dec 2005 2:24 PM

Ugur Ekinci

Hi ,
I have two Sql Server 2000 on seperate machines ,
First one accepts datetime format like ("dd.MM.yyyy hh:mm:ss")
And Second one accepts datetime format like ("MM.dd.yyyy hh:mm:ss")

1- Date formats are different because of SQL Collation?
2- Do regional Settings affect Sql date format?
3- (Important) When inserting a datetime into first server there is no
problem (15.12.2000 12:12:12) , but when I insert into second server (if day
is greater then 12 ) the following error occurs
"The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value"

So each time I format the datetime value to ("dd.MM.yyyy hh:mm:ss") when
inserting into second server,

I don't want to change date Format each time manuelly
So I think ,I need to know that in code behind Which date format SQL server
accept?
My app is ASP.NET with VB.NET

I use System.DateTime , should I use System.Data.SqlTypes.SqlDateTime ?
Any idea ?

Best regards...

16 Dec 2005 3:42 PM

AdrianJMartin

Always format dates to include the month as a NAME it makes some many
problems like this go away!

eg "dd MMM yyyy hh.mm.ss" or "MMM dd yyyy hh.mm.ss"

sql server( and most other databases) then has enough to go on when in
converts the string to a datetime.

i assume you are building the sql string in code, if you use dataadapters
and parameters then dot net will do the culture stuff for you.

Show quote

"Ugur Ekinci" <u***@argeset.com> wrote in message
news:%23RfwexkAGHA.204@TK2MSFTNGP15.phx.gbl...
> Hi ,
> I have two Sql Server 2000 on seperate machines ,
> First one accepts datetime format like ("dd.MM.yyyy hh:mm:ss")
> And Second one accepts datetime format like ("MM.dd.yyyy hh:mm:ss")
>
> 1- Date formats are different because of SQL Collation?
> 2- Do regional Settings affect Sql date format?
> 3- (Important) When inserting a datetime into first server there is no
> problem (15.12.2000 12:12:12) , but when I insert into second server (if
> day is greater then 12 ) the following error occurs
> "The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value"
>
>
> So each time I format the datetime value to ("dd.MM.yyyy hh:mm:ss") when
> inserting into second server,
>
> I don't want to change date Format each time manuelly
> So I think ,I need to know that in code behind Which date format SQL
> server accept?
> My app is ASP.NET with VB.NET
>
> I use System.DateTime , should I use System.Data.SqlTypes.SqlDateTime ?
> Any idea ?
>
> Best regards...
>
>
>
>
>
>