在SQL Server数据库上更改dateformat，而不是将整个服务器更改为dd / mm / yyyy [英] Changing dateformat on a SQL Server DB and not on whole server to dd/mm/yyyy

问题描述

任何人都可以帮助，我们有一个sql server 2005数据库安装（实际上是一个sql server 2000附加到sql server 2005服务器）与默认服务器语言的美国与日期格式像mm / dd / yy，我们真的需要保持这种方式，但目前只是将新的数据库上传到服务器，这需要具有dd / mm / yyyy的dateformat。

我可以强制更改一个数据库而不是整个服务器？如果我强制整个服务器上的更改将使我的所有其他应用程序失败

例如，目前我们有这个sql语句失败..

SELECT * FROM sesiones WHERE ultimo_acceso< '16 / 04/2009 13:36:17'

但是当然我们可以添加这个现在可以使用的东西

SELECT * FROM sesiones WHERE ultimo_acceso<转换（datetime，'16 / 04/2009 13:36:17'，103）

但是问题在于内部存在大量的sql语句应用。事实是，应用程序是相当老的，我们不想对源代码进行任何更改。

因此，如果我们可以强制更改只是在数据库/特定数据库的表，那么这将足够了

任何帮助真的赞赏

解决方案

您可以通过在SQL Management Studio>安全性>登录> {用户中为该用户选择默认语言来更改每个用户的默认日期格式属性}>默认语言

您可以使用

查看每种语言的日期格式 EXEC sp_helplanguage 
  

can anyone help, we have a sql server 2005 database installed (actually its an sql server 2000 attached to an sql server 2005 server) with the default server language language USA with dateformat like mm/dd/yy and we really need to keep it this way but currently just uploaded a new database to the server and this needs to have the dateformat for dd/mm/yyyy.

Can i force a change just on a database and not the whole server? If i force the change on the whole server it will make all my other apps fail

For example currently we have this sql statement which fails..

SELECT * FROM sesiones WHERE ultimo_acceso < '16/04/2009 13:36:17'

but of course we can add this which now works

SELECT * FROM sesiones WHERE ultimo_acceso < Convert(datetime, '16/04/2009 13:36:17', 103)

but the problem being is that there are a large number of sql statements within the application. The truth being is that the application is fairly old and we don't really want to make any changes to the source code..

SO hence if we could force a a change just on the Database / the tables of specific database then this would suffice

Any help really appreciated

解决方案

You can change the default date format per user by selecting the default language for that user in SQL Management Studio > Security > Logins > {user properties} > Default language.

And you can see the date format for each language with

EXEC sp_helplanguage

这篇关于在SQL Server数据库上更改dateformat，而不是将整个服务器更改为dd / mm / yyyy的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！