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

適用於: 是SQL Server (從 2008 開始) 是Azure SQL Database 是Azure SQL 資料倉儲 是平行處理資料倉儲 APPLIES TO: yesSQL Server (starting with 2008) 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 (預設,U.S.英文)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

需要 public 角色的成員資格。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)