SQL Server Solutions - Change The First Day Of The Week For SQL Server (SQL Server 2000)


You've probably seen those business calendars that show Monday as the first day of the week, right? But SQL Server assumes Sunday is the first day of the week, by default. Is it possible to change this to fit your own business needs--such as resetting the first day to Monday? Yes, it's not only possible, but it's quite easy. You can use the SET DATEFIRST system configuration function to do this. To set Monday as the first day of the week for the purposes of SQL Server date manipulations, just use this statement:


  • SET DATEFIRST 1 -- Monday; Use 2 for Tuesday, 3 for Wednesday, etc.

Then, you can also check this value programmatically by using the built-in @@DATEFIRST variable. Once you've changed the internal first day of the week, SQL Server uses this value for all its day-of-the-week date calculations. For example, try out the following code (on a Monday):


  • SET DATEFIRST 1 -- Monday (which is today)
  • SELECT Today = DATEPART(dw, GETDATE()) -- Returns 1
  •  
  • SET DATEFIRST 7 -- Back to Sunday, the default (but today is still Monday)
  • SELECT Today = DATEPART(dw, GETDATE()) -- Returns 2

Go back