oracle 显示在 10 月的星期日雇用的员工姓名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33760175/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Display employee name who were hired on Sunday of October Month
提问by Sharzil Shaikh
My exercise says to display employee names who were hired on sunday in the month of October
我的练习说要显示在 10 月的星期日被录用的员工姓名
select ename,hiredate from emp where
to_char(hiredate,'MON')='OCTOBER' AND to_char(hiredate,'DAY')='SUNDAY'
Also
还
Display all the employess who are hired in last quarter.
显示上一季度雇用的所有员工。
In oracle we cannot use qq so how to write a query for this?
在 oracle 中我们不能使用 qq 那么如何为此编写查询？
回答by Alex Poole
The MON date format is the abbreviated month name, in the current sesison's NLS date language. You can can compare the abbreviation with the short name, or the full month name:
MON 日期格式是缩写的月份名称，采用当前 sesison 的 NLS 日期语言。您可以将缩写与短名称或完整月份名称进行比较：
to_char(hiredate,'MON')='OCT'
or
或者
to_char(hiredate,'FMMONTH')='OCTOBER'
The 'FM' format modifierstops it adding trailing spaces, which it does by default, up to the length of the longest possible value. If you don't do that you either have to pad your fixed value, or trim the result. (Note that for some languages the abbreviations need FM for comparison too as even their length can change; not in English as the MON and DY values are all 3 characters; but French month abbreviations, for example, can be 3 or 4 characters).
的“FM”格式改性剂停止其添加尾部空格，其中它不通过默认，最多最长的可能值的长度。如果你不这样做，你要么必须填充你的固定值，要么修剪结果。（请注意，对于某些语言，缩写也需要 FM 进行比较，因为它们的长度也可以更改；不是英语，因为 MON 和 DY 值都是 3 个字符；但例如，法语月份缩写可以是 3 或 4 个字符）。
It's generally better to use month numbers for this though:
不过，通常最好使用月份数字：
to_char(hiredate,'MM')='10'
That will get you October in any year; if you want this October you can include the year:
这将使您在任何一年都有十月；如果你想要这个十月，你可以包括这一年：
to_char(hiredate,'YYYYMM')='201510'
Or if you want the previous month of the current year you can use:
或者，如果您想要当年的上个月，您可以使用：
trunc(hire_date, 'MM') = add_months(trunc(sysdate, 'MM'), -1)
The same thing happens with the day; the DAY format pads to the longest day name by default, so you need:
白天也会发生同样的事情；DAY 格式默认填充为最长的一天名称，因此您需要：
to_char(hiredate,'DY')='SUN'
or
或者
to_char(hiredate,'FMDAY')='SUNDAY'
You can use day numbers here but they are locale-dependent too. If you're gong to use names it's safer to specify the language:
您可以在此处使用天数，但它们也取决于语言环境。如果您要使用名称，则指定语言更安全：
to_char(hire_date,'FMDAY','NLS_DATE_LANGUAGE=''ENGLISH''')='SUNDAY'
Oracle does have the Q date format model, fixed to calendar months - so January-March is Q1. You may want financial quarters; or may want the previous three months; or something else.
Oracle 确实有Q 日期格式模型，固定为日历月 - 所以一月至三月是 Q1。您可能需要财务季度；或者可能需要前三个月；或者是其他东西。
回答by Thorsten Kettner
You get the quarter with TO_CHAR('Q'). So get that quarter from sysdate and calculate the previous:
您可以使用 TO_CHAR('Q') 获得季度。因此，从 sysdate 获取该季度并计算前一个：
select ename,hiredate
from emp
where to_char(hiredate, 'q') =
case to_char(sysdate, 'q')
when '1' then '4'
when '2' then '1'
when '3' then '2'
when '4' then '3'
end
and extract(year from hiredate) =
case when to_char(sysdate, 'q') = '1' then
extract(year from sysdate) - 1
else
extract(year from sysdate)
end;
As to Sunday in October, your query looks fine, but you rely on English settings for the database. Better use numbers where possible or specify the language used:
至于十月的星期天，您的查询看起来不错，但您依赖于数据库的英文设置。在可能的情况下更好地使用数字或指定使用的语言：
select ename, hiredate
from emp
where to_char(hiredate,'mm') = 10
and to_char(hiredate, 'FMDAY', 'nls_date_language = american') = 'SUNDAY';
EDIT: I just read your question again and come to think: What does "last" quarter mean? The quarter before this, as I assumed, or quarter 4 of a year? That would be simply where to_char(sysdate, 'q') = '4'.
编辑：我刚刚再次阅读您的问题并开始思考：“上一个”季度是什么意思？之前的一个季度，如我所假设的，还是一年的第 4 季度？那将是简单的where to_char(sysdate, 'q') = '4'。
EDIT 2: Had to change 'DAY' to 'FMDAY'. See Alex Poole's answer for an explanation.
编辑 2：必须将“DAY”更改为“FMDAY”。有关解释，请参阅 Alex Poole 的回答。
回答by SomeJavaGuy
to know if the hiredate is in the last quarter you could check if the month is greater or equal to 10
要知道受雇者是否在最后一个季度，您可以检查该月是否大于或等于 10
select ename,
hiredate
from emp
where to_char(hiredate,'MM')>=10
For your other question, as statet here, MONreturn an abbreviated name of month. This causes that OCTOBERwont be the output of MON. You are either out for MONTHin your to_char, or you would want to compare it to OCTinstead. As an alternative you could probably do it like this. If so keep @ThrostenKettner′s comment in mind.
对于您的另一个问题，如 statet here，MON返回月份的缩写名称。这将导致OCTOBER不会是MON. 你要么MONTH在你的 中to_char，要么你想将它与它进行比较OCT。作为替代方案，您可能可以这样做。如果是这样，请记住@ThrostenKettner 的评论。
select ename,
hiredate
from emp
where to_char(hiredate,'MM')=10
and to_char(hiredate,'D')=1
回答by Arjun Pawar
this is one type of logic to solve this question
这是解决这个问题的一种逻辑
please check if you want to use full thanks
请检查您是否要使用完整的谢谢
SELECT ee.*,to_char(ee.hiredate,'Q') Quarter FROM EMPLOYEES ee WHERE TO_CHAR(ee.HIREDATE,'DAY') IN (SELECT TO_CHAR(NEXT_DAY(dd.HIREDATE,'MON'),'DAY') FROM EMPLOYEES dd)
SELECT ee.*,to_char(ee.hiredate,'Q') Quarter FROM EMPLOYEES ee WHERE TO_CHAR(ee.HIREDATE,'DAY') IN (SELECT TO_CHAR(NEXT_DAY(dd.HIREDATE,'MON'),'DAY')来自员工 dd)
