如何获取SQL Server使用用户区域设置日期格式的查询？ [英] How can I get SQL Server to use User Locale for Date format for a Query?

问题描述

我为此测试创建了以下简单表格：

I have created the following simple table for this test:

DROP TABLE DateFieldTest
CREATE TABLE DateFieldTest
(
    DateField Date NOT NULL
)

INSERT INTO DateFieldTest VALUES ('2013-07-15')
INSERT INTO DateFieldTest VALUES ('2013-07-16')
INSERT INTO DateFieldTest VALUES ('2013-07-17')

INSERT INTO DateFieldTest VALUES ('2013-07-05')
INSERT INTO DateFieldTest VALUES ('2013-07-06')
INSERT INTO DateFieldTest VALUES ('2013-07-07')

INSERT INTO DateFieldTest VALUES ('2013-06-05')
INSERT INTO DateFieldTest VALUES ('2013-06-06')
INSERT INTO DateFieldTest VALUES ('2013-06-07')

INSERT INTO DateFieldTest VALUES ('2013-05-05')
INSERT INTO DateFieldTest VALUES ('2013-05-06')
INSERT INTO DateFieldTest VALUES ('2013-05-07')

SELECT * from DateFieldTest where DateField >= '2013-07-05'
SELECT * from DateFieldTest where DateField >= '2013-07-06'
SELECT * from DateFieldTest where DateField >= '2013-07-07'

SELECT * from DateFieldTest where DateField >= '05/07/2013'
SELECT * from DateFieldTest where DateField >= '06/07/2013'
SELECT * from DateFieldTest where DateField >= '07/07/2013'

SELECT * from DateFieldTest where DateField >= '07/05/2013'
SELECT * from DateFieldTest where DateField >= '07/06/2013'
SELECT * from DateFieldTest where DateField >= '07/07/2013'

SELECT * from DateFieldTest where DateField >= '24/04/2013'

除最后一个查询之外的所有查询执行。我的问题是，我的不同的用户有不同的日期格式，一些用户的日期格式是dd / mm / yyyy，而不是美国/加拿大的标准mm / dd / yyyyy。

All the queries except the last one execute. The problem I have is that my different users have different date formats, and some users date format is dd/mm/yyyy, not the standard US/Canada of mm/dd/yyyyy.

我知道我可以使用convert函数来转换日期，但是我使用的是较旧的查询和软件，它只是使用用户输入的日期。我想知道如何指示SQL Server 2005+在其语言环境中使用用户的短日期格式。

I know I can use the convert function to cast a date, but I am working with older queries and software that is simply using the date entered by the user. I want to know how to instruct SQL Server 2005+ to use the user's Short Date format in their locale.

推荐答案

日期格式使用 SET DATEFORMAT XXX

SET DATEFORMAT DMY
SELECT * from DateFieldTest where DateField >= '24/04/2013'

http://msdn.microsoft.com/fr-fr/library/ms189491.aspx

这篇关于如何获取SQL Server使用用户区域设置日期格式的查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！