SET DATEFIRST (Transact-SQL)SET DATEFIRST (Transact-SQL)

适用对象: yesSQL ServeryesAzure SQL 数据库yesAzure SQL 数据仓库yes并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

将一周的第一天设置为从 1 到 7 的一个数字。Sets the first day of the week to a number from 1 through 7.

有关所有 Transact-SQLTransact-SQL 日期和时间数据类型及函数的概述,请参阅日期和时间数据类型及函数 (Transact-SQL)For an overview of all Transact-SQLTransact-SQL date and time data types and functions, see Date and Time Data Types and Functions (Transact-SQL).

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

-- Syntax for SQL Server and Azure SQL Database  
  
SET DATEFIRST { number | @number_var }   
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse  
  
SET DATEFIRST 7 ;  

参数Arguments

number | @number_varnumber | @number_var
指示一周的第一天的一个整数。Is an integer that indicates the first day of the week. 可以是下列值之一。It can be one of the following values.

ReplTest1Value 一周的第一天是First day of the week is
11 星期一Monday
22 星期二Tuesday
33 星期三Wednesday
44 星期四Thursday
55 星期五Friday
66 星期六Saturday
7英语)7 (default, U.S. English) 星期日Sunday

RemarksRemarks

若要查看 SET DATEFIRST 的当前设置,请使用 @@DATEFIRST 函数。To see the current setting of SET DATEFIRST, use the @@DATEFIRST function.

SET DATEFIRST 的设置是在执行或运行时设置,而不是在分析时设置。The setting of SET DATEFIRST is set at execute or run time and not at parse time.

指定 SET DATEFIRST 对 DATEDIFF 不起作用。Specifying SET DATEFIRST has no effect on DATEDIFF. DATEDIFF 始终使用星期日作为每周的第一天,以确保函数是确定性的。DATEDIFF always uses Sunday as the first day of the week to ensure the function is deterministic.

权限Permissions

要求 公共 角色具有成员身份。Requires membership in the public role.

示例Examples

以下示例显示了某个日期值对应的一周中的一天,并显示了更改 DATEFIRST 设置的效果。The following example displays the day of the week for a date value and shows the effects of changing the DATEFIRST setting.

-- SET DATEFIRST to U.S. English default value of 7.  
SET DATEFIRST 7;  
  
SELECT CAST('1999-1-1' AS datetime2) AS SelectDate  
    ,DATEPART(dw, '1999-1-1') AS DayOfWeek;  
-- January 1, 1999 is a Friday. Because the U.S. English default   
-- specifies Sunday as the first day of the week, DATEPART of 1999-1-1  
-- (Friday) yields a value of 6, because Friday is the sixth day of the   
-- week when you start with Sunday as day 1.  
  
SET DATEFIRST 3;  
-- Because Wednesday is now considered the first day of the week,  
-- DATEPART now shows that 1999-1-1 (a Friday) is the third day of the   
-- week. The following DATEPART function should return a value of 3.  
SELECT CAST('1999-1-1' AS datetime2) AS SelectDate  
    ,DATEPART(dw, '1999-1-1') AS DayOfWeek;  
GO  

另请参阅See Also

SET 语句 (Transact-SQL)SET Statements (Transact-SQL)