Day of the week: Difference between revisions
→{{header|SQL}}
Line 5,314:
=={{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}}==
<syntaxhighlight lang="sql">WITH RECURSIVE cte AS (
| |||