SET DATEFIRST (Transact-SQL)

Sets the first day of the week to a number from 1 through 7.

Transact-SQL Syntax Conventions

Syntax

SET DATEFIRST { number | @number_var } 

Arguments

• number | **@**number_var
  Is an integer that indicates the first day of the week. It can be one of the following values.

  Value	First day of the week is
  1	Monday
  2	Tuesday
  3	Wednesday
  4	Thursday
  5	Friday
  6	Saturday
  7 (default, U.S. English)	Sunday

Remarks

Use the @@DATEFIRST function to see the current setting of SET DATEFIRST.

The setting of SET DATEFIRST is set at execute or run time and not at parse time.

Permissions

Requires membership in the public role.

Examples

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('1/1/1999' AS DATETIME) AS SelectDate, DATEPART(dw, '1/1/1999') 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 1/1/1999 
-- (Friday) yields a value of 6, because Friday is the sixth day of the 
-- week when starting with Sunday as day 1.

SET DATEFIRST 3;
-- Because Wednesday is now considered the first day of the week,
-- DATEPART should now show that 1/1/1999 (a Friday) is the third day of the 
-- week. The following DATEPART function should return a value of 3.
SELECT CAST('1/1/1999' AS DATETIME) AS SelectDate, DATEPART(dw, '1/1/1999') AS DayOfWeek;
GO

See Also

Reference

Data Types (Transact-SQL)
@@DATEFIRST (Transact-SQL)
Date and Time (Transact-SQL)
SET (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance