Prev: Linked Server and Stored Procedure
Next: Distinct Count Help
From: Cetel Sistemas on 2 Jul 2008 06:50
Hello:

I have a web application with a lot of stores procedures running under SQL
Server 2000 in English but the languaje of the application is Spanish. In
many of these stored procedures there are transformations of text dates like
convert(datetime,'01/07/2008 19:45:23') before I store it in a datetime
field. All is OK and the field have de value July 1.

I have pre-migrated the application to a SQL Server 2005 in English but now,
this statement convert(datetime,'01/07/2008 19:45:23') stores the date
January 7. If I change the statement like convert(datetime,'01/07/2008
19:45:23',103) the stored result is OK again (July 1).

My question is if there is some configuration issue, parameter, etc at
server or database in order I have not to change code in my application.

Thanks in advance.

Jesús Corbí
From: Tibor Karaszi on 2 Jul 2008 07:06
The interpretation is determined by the language for the login who is executing the command. All
explained in:
http://www.karaszi.com/SQLServer/info_datetime.asp


--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Cetel Sistemas" <CetelSistemas(a)discussions.microsoft.com> wrote in message
news:9D47E5E7-A4BB-43CE-AE0A-61A014987E48(a)microsoft.com...
> Hello:
>
> I have a web application with a lot of stores procedures running under SQL
> Server 2000 in English but the languaje of the application is Spanish. In
> many of these stored procedures there are transformations of text dates like
> convert(datetime,'01/07/2008 19:45:23') before I store it in a datetime
> field. All is OK and the field have de value July 1.
>
> I have pre-migrated the application to a SQL Server 2005 in English but now,
> this statement convert(datetime,'01/07/2008 19:45:23') stores the date
> January 7. If I change the statement like convert(datetime,'01/07/2008
> 19:45:23',103) the stored result is OK again (July 1).
>
> My question is if there is some configuration issue, parameter, etc at
> server or database in order I have not to change code in my application.
>
> Thanks in advance.
>
> Jesús Corbí

From: Aaron Bertrand [SQL Server MVP] on 2 Jul 2008 08:05
If you pass the value as a datetime and don't deal with converting a string,
you shouldn't have to worry about it.

If you are going to be passing strings then I strongly recommend using
unambiguous formats that are not prone to misinterpretation due to language
settings, regional settings, dateformat settings, etc. For date without
time, the safest format is 'YYYYMMDD'. With time, it is
'YYYY-MM-DDTHH:MM:SS.mmm'... Tibor's article certainly goes into more
detail.



On 7/2/08 6:50 AM, in article
9D47E5E7-A4BB-43CE-AE0A-61A014987E48(a)microsoft.com, "Cetel Sistemas"
<CetelSistemas(a)discussions.microsoft.com> wrote:

> Hello:
>
> I have a web application with a lot of stores procedures running under SQL
> Server 2000 in English but the languaje of the application is Spanish. In
> many of these stored procedures there are transformations of text dates like
> convert(datetime,'01/07/2008 19:45:23') before I store it in a datetime
> field. All is OK and the field have de value July 1.
>
> I have pre-migrated the application to a SQL Server 2005 in English but now,
> this statement convert(datetime,'01/07/2008 19:45:23') stores the date
> January 7. If I change the statement like convert(datetime,'01/07/2008
> 19:45:23',103) the stored result is OK again (July 1).
>
> My question is if there is some configuration issue, parameter, etc at
> server or database in order I have not to change code in my application.
>
> Thanks in advance.
>
> Jes�s Corb�

 | 
Pages: 1
Prev: Linked Server and Stored Procedure
Next: Distinct Count Help