Day of the week: Difference between revisions
Content added Content deleted
Line 5,314: | Line 5,314: | ||
=={{header|SQL}}== |
=={{header|SQL}}== |
||
SQL has good support for date functions; care must be taken with NLS settings (globalization support), in the code below the date format language is passed in as an argument to the relevant function. (Or, see a variation that does not depend on language settings, after the output shown below.) |
|||
<syntaxhighlight lang="sql">select extract(year from dt) as year_with_xmas_on_sunday |
|||
from ( |
|||
select add_months(date '2008-12-25', 12 * (level - 1)) as dt |
|||
from dual |
|||
connect by level <= 2121 - 2008 + 1 |
|||
) |
|||
where to_char(dt, 'Dy', 'nls_date_language=English') = 'Sun' |
|||
order by 1 |
|||
;</syntaxhighlight> |
|||
{{out}} |
|||
<pre> |
|||
YEAR_WITH_XMAS_ON_SUNDAY |
|||
------------------------ |
|||
2011 |
|||
2016 |
|||
2022 |
|||
2033 |
|||
2039 |
|||
2044 |
|||
2050 |
|||
2061 |
|||
2067 |
|||
2072 |
|||
2078 |
|||
2089 |
|||
2095 |
|||
2101 |
|||
2107 |
|||
2112 |
|||
2118 |
|||
17 rows selected.</pre> |
|||
Alternatively, the WHERE clause can be written in a way that avoids the complication of language settings. The (overloaded) TRUNC function, as applied to dates, takes a second argument indicating "to what" we must truncate. One option is 'iw' for "ISO week"; this truncates to the most recent Monday (the beginning of the ISO standard week, which is Monday through Sunday by definition). Like so (replace in the query above): |
|||
<syntaxhighlight lang="sql">where dt - trunc(dt, 'iw') = 6</syntaxhighlight> |
|||
=={{header|SQLite3}}== |
=={{header|SQLite3}}== |
||
<syntaxhighlight lang="sql">WITH RECURSIVE cte AS ( |
<syntaxhighlight lang="sql">WITH RECURSIVE cte AS ( |