05:38

# The first day of the week page 1

Task. Find out the date that corresponds to the first Monday of January, 2013.

Under some assumptions the task can be solved as follows:

Console
Execute
1. WITH num(n) AS(/* With aid of recursive CTE get a table having single column n
2.                      with sequence of integer values from 0 to 6 */
3. SELECT 0
4. UNION ALL
5. SELECT n+1 FROM num
6. WHERE n < 6),
7. dat AS (/* Getting table with dates from January 1 to 7, 2013  */
8. SELECT DATEADD(dd,  n,  CAST('2013-01-01' AS DATETIME)) AS day FROM num
9. )
10. SELECT day FROM dat  WHERE DATEPART(dw, day) = 1; /* Taking the day that corresponds
11.                         to the first day of a week */

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

day
2013-01-06
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:

Console
Execute
1. WITH num(n) AS (SELECT 0
2. UNION ALL
3. SELECT n+1 FROM num
4. WHERE n < 6),
5. dat AS (
6. SELECT DATEADD(dd,  n,  CAST('2013-01-01' AS DATETIME)) AS day FROM num
7. )
8. SELECT day FROM dat  WHERE DATENAME(dw, day) = 'monday';

day
2013-01-07

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

1. SET LANGUAGE german;
we'll get empty row set because the last line in the query should be written as

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

 Pages 1 2