The first day of the week

Under some assumptions the task can be solved as follows:

WITH num(n) AS
(/* создаем таблицу со столбцом n и значениями от 0 до 6 */
    SELECT 0
    UNION ALL
    SELECT n+1 FROM num
    WHERE n < 6
),
dat AS 
(/* создаем таблицу с датами от 1 до 7 января 2013 года */
    SELECT DATEADD(dd, n, CAST('2013-01-01' AS DATETIME)) AS day 
    FROM num
)
SELECT day 
FROM dat 
WHERE DATEPART(dw, day) = 1; /* выбираем день, соответствующий первому дню недели */

The assumption we mentioned above adopts Monday as the first day of a week. However, running of the query on the sql-ex.ru site gives us

But it is Sunday. This is due to site’s settings which reason that a Sunday is the first day of a week. Could we write a solution that does not depend on server settings? Let’s try to take day name instead of week day’s number when filtering Monday:

WITH num(n) AS 
(
    SELECT 0
    UNION ALL
    SELECT n+1 FROM num
    WHERE n < 6
),
dat AS (
    SELECT DATEADD(dd, n, CAST('2013-01-01' AS DATETIME)) AS day 
    FROM num
)
SELECT day FROM dat WHERE DATENAME(dw, day) = 'monday';

This is true now, but will this query be true always if we shall embed it into application code? The answer is no. If language settings will be changed

SET
LANGUAGE german;

we’ll get empty row set because the last line in the query should be written as

SELECT day FROM dat WHERE DATENAME(dw, day) ='Montag';

@@DATEFIRST function

@@DATEFIRST returns an integer which defines the first day of a week for the current session. In so doing, 1 is corresponding to Monday, but 7 - to Sunday respectively. I.e. if

SELECT
@@DATEFIRST;

returns 7, the first day of a week will be Sunday (in according with current site’s settings).

To make our solution to the task be independent on the first-day-of-week settings, we need to use @@DATEFIRST function, for example

WITH num(n) AS
(
    SELECT 0
    UNION ALL
    SELECT n+1 
    FROM num
    WHERE n < 6
),
dat AS (
    SELECT DATEADD(dd, n, CAST('2013-01-01' AS DATETIME)) AS day 
    FROM num
)
SELECT day, DATENAME(dw, day) week_day 
FROM dat 
WHERE DATEPART(dw, day) = 1+(8-@@DATEFIRST) % 7;

To change value of the first day of a week (for a current session), we can use SET DATEFIRST statement. So, if we run the following script

SET DATEFIRST 1;
SELECT @@DATEFIRST;

we’ll obtain 1, i.e. week starts with Monday now.

The first-day-of-a-week settings may be changed through the changing of session language. For example, running

SET LANGUAGE us_english;
SELECT
@@DATEFIRST;

lead us to 7 (Sunday) once more, as this value is default for U.S. English, whereas choosing German will do Monday to be the first day of a week.

Nevertheless you can change the language parameter and first day of a week simultaneously to obtain, say, English and a week that will start with Monday:

SET DATEFIRST 1;
SET LANGUAGE us_english;
SELECT @@DATEFIRST;

Language settings impact specifically on the string representation of date/time components. Compare these results:

DECLARE @dt DATE = '2012-12-17'; -- December 17, 2012
SET LANGUAGE us_english;
SELECT DATENAME(DW, @dt) AS day_of_week, DATENAME(MONTH,@dt) AS month;

SET LANGUAGE german;
SELECT DATENAME(DW, @dt) AS day_of_week, DATENAME(MONTH, @dt) AS month;

Conclusion. If you want to have queries that always give correct results for any settings of server or database, you can specify necessary setting parameters for the session in which these queries are executing; or you need to check the values of corresponding parameters (@@DATEFIRST as example) in each such a query.

declare @anchor_date datetime
declare @reference_date datetime  
  
select @anchor_date='19000101', @reference_date='20130505'  
  
SELECT DATEADD(day, DATEDIFF(day, @anchor_date,  
DATEADD(year, DATEDIFF(year, '19000101', @reference_date), '19000101') - 1) /7*7 + 7,
@anchor_date);

SELECT day FROM dat WHERE DATEPART(dw, day) = DATEPART(dw, '19000101');

SELECT day FROM dat WHERE DATENAME(dw, day) = DATENAME(dw, '19000101');

Seggested exercises: 78, 99, 110, 118