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 

Learn more tricks

Add Comment

Comments

3rd December 2013

/* No matter how @@DATEFIRST is return result as weekdayName,weekNumber Mo 1 Tu 2 Wn 3 Th 4 Fr 5 Sa 6 Su 7 */ CREATE FUNCTION dbo.fnFixWeekday ( @currentDate date ) RETURNS INT AS BEGIN -- get DATEFIRST setting DECLARE @ds int = @@DATEFIRST -- get week day number under current DATEFIRST setting DECLARE @dow int = DATEPART(dw,@currentDate) RETURN 1+(((@dow+@ds) % 7)+5) % 7 END

JG Jin