-- SET DATEFIRST to U.S. English default value of 7.
SET DATEFIRST 7;
SELECT
@@DATEFIRST;
SELECT
GETDATE()
, DATEPART(dw , GETDATE()) 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 1999-1-1
-- (Friday) yields a value of 6, because Friday is the sixth day of the
-- week when you start with Sunday as day 1.
SET DATEFIRST 3;
SELECT
@@DATEFIRST;
-- Because Wednesday is now considered the first day of the week,
-- DATEPART now shows that 1999-1-1 (a Friday) is the third day of the
-- week. The following DATEPART function should return a value of 3.
SELECT
GETDATE()
, DATEPART(dw , GETDATE()) AS DayOfWeek;
SET DATEFIRST 7;
How do we handle getting the DATEPART (1 = Sunday always) irregardless of DATEFIRST setting?
I really don't want to do a case and subtract...
this always seems ridiculous to me, how about
select datediff(day,0, getdate()) % 7
where 6 represents Sunday
or you could do
select (datediff(day,0, '2016-07-31') - 5) % 7
to get Sun = 1, Mon = 2, Tue = 3 ... etc
or you could do this fiddle
select (datepart(weekday,get_date()) + @@datefirst - 1) % 7 + 1
seems to work for all datepart
set datefirst 5
select (datepart(weekday,'2016-07-31') + @@datefirst - 1) % 7 + 1
select (datepart(weekday,'2016-08-01') + @@datefirst - 1) % 7 + 1
select (datepart(weekday,'2016-08-02') + @@datefirst - 1) % 7 + 1
select (datepart(weekday,'2016-08-03') + @@datefirst - 1) % 7 + 1
select (datepart(weekday,'2016-08-04') + @@datefirst - 1) % 7 + 1
select (datepart(weekday,'2016-08-05') + @@datefirst - 1) % 7 + 1
select (datepart(weekday,'2016-08-06') + @@datefirst - 1) % 7 + 1
select (datepart(weekday,'2016-08-07') + @@datefirst - 1) % 7 + 1