如何查找所有第一个星期六，星期日，星期一，星期二，星期五

问题描述

如何在SQL中查找特定月份中的所有第一个，第二个，第三个和第四个星期六，星期日，星期一，星期二和星期五，输入值来自数据列?

How to find all first, second, third and four Saturday, Sunday, Monday, Tuesday and Friday in particular month in SQL, input value is from a data column?

推荐答案

For this example we’ll use sysdate as input but any Oracle date will work. You can also substitute any other day of the week for Monday.

The first day of the month is probably a good place to start:

SQL> select sysdate from dual;

SYSDATE
---------
18-JUL-06

SQL> select trunc(sysdate, 'MONTH') FROM DUAL;

TRUNC(SYS
---------
01-JUL-06

Now that we’ve got that we can find the first Monday with the next_day function. Of course we need to remember the next_day function looks for the next named day after the date provided so we subtract 1 day from the date in case the first is a Monday.

SQL> select next_day(trunc(sysdate, 'MONTH')-1, 'Monday') from dual;

NEXT_DAY(
---------
03-JUL-06

Now that we have the first Monday of the month we can add 7 days to find the second Monday or 14 to find the third.

SQL> select next_day(trunc(sysdate, 'MONTH')-1, 'Monday')+7 FROM dual;

NEXT_DAY(
---------
10-JUL-06

SQL> select next_day(trunc(sysdate, 'MONTH')-1, 'Monday')+14 FROM dual;

NEXT_DAY(
---------
17-JUL-06

So from here you can change the day you’re looking for or the week number you want it in.

这篇关于如何查找所有第一个星期六，星期日，星期一，星期二，星期五的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！