DATEPART and SET DATEFIRST (Level 4)
|SQL Server 6.x||SQL Server 2000|
|The SET DATEFIRST setting of the DATEPART function had no effect on the week datepart.||The week datepart may give values different from earlier versions of Microsoft® SQL Server™. However, any difference will appear only if the SET DATEFIRST setting is not the default (the U.S. English default is 7).
If the year provided in the DATEPART function has 366 days, a week value of 54 can be returned if the first week of the year starts on a Saturday, and the year ends on the same day of the week with the first day of the week counted from Sunday.
When using the ISO 8601 standard, week values are always from 1 through 53, as the first week of the year is guaranteed to have a minimum of 4 days.
Expect different results as compared to earlier versions of SQL Server. Use the default value for SET DATEFIRST so that DATEPART returns the expected results for the week datepart. Otherwise, DATEPART values will be one less than expected.