loading..
Ðóññêèé    English
13:44

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';

Bookmark and Share
Pages 1 2
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
month ago
ïðîäàòü payeer . àãåíòñòâî íåäâèæèìîñòè Happyluxhome
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.