The assumption we mentioned above adopts Monday as the first day of a week. However, running of the query on the 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
WITH num(n)AS(SELECT0
UNIONALL
SELECT n+1FROM 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
SETLANGUAGE 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';
go
Console
