MS SQL Tips & Tricks

How to get the week day regardless @@DATEFIRST setting

Getting the week day of given date might be confusing. You can use DATEPART() function but it depends on @@DATEFIRST parameter that can be set up differently on different server installation. @@DATEFIRST parameters sets day of the week that is considered to be the first day of the week. The most configurations are set to point for Sunday as a first day (@@DATEFIRST = 7) of the week.

To avoid this setting You can use below calculation that regardless @@DATEFIRST setting will always return 1 for Monday, 2 for Tuesday etc. 

declare @cDate datetime;
set @cDate = GetDate()
select  (@@DATEFIRST-1)+DATEPART(dw, @cDate)- 
		case 
			when (@@DATEFIRST-1)+
				DATEPART(dw, @cDate)>7 then 7 
			else 0 
		end

See also


Learn more tricks

Add Comment

Comments