比较 Oracle SQL 中的日期

Comparing Dates in Oracle SQL

提问by user1336830

I'm trying to get it to display the number of employees that are hired after June 20, 1994, But I get an error saying "JUN' invalid identifier. Please help, thanks!

我试图让它显示 1994 年 6 月 20 日之后雇用的员工人数，但我收到一条错误消息，提示“JUN”无效标识符。请帮忙，谢谢！

Select employee_id, count(*)
From Employee
Where to_char(employee_date_hired, 'DD-MON-YY') > 31-DEC-95; 

回答by Ben

31-DEC-95isn't a string, nor is 20-JUN-94. They're numbers with some extra stuff added on the end. This should be '31-DEC-95'or '20-JUN-94'- note the single quote, '. This will enable you to do a string comparison.

31-DEC-95不是字符串，也不是20-JUN-94. 它们是在末尾添加了一些额外内容的数字。这应该是'31-DEC-95'或'20-JUN-94'- 注意单引号，'。这将使您能够进行字符串比较。

However, you're not doing a string comparison; you're doing a date comparison. You should transform your string into a date. Either by using the built-in TO_DATE()function, or a date literal.

但是，您不是在进行字符串比较；你在做一个日期比较。您应该将字符串转换为日期。通过使用内置TO_DATE()函数或日期文字。

TO_DATE()

迄今为止（）

select employee_id
  from employee
 where employee_date_hired > to_date('31-DEC-95','DD-MON-YY')

This method has a few unnecessary pitfalls

这种方法有一些不必要的陷阱

• As a_horse_with_no_name noted in the comments, DEC, doesn't necessarily mean December. It depends on your NLS_DATE_LANGUAGEand NLS_DATE_FORMATsettings. To ensure that your comparison with work in any locale you can use the datetime format modelMMinstead
• The year '95 is inexact. You know you mean 1995, but what if it was '50, is that 1950 or 2050? It's always best to be explicit

• 正如评论中提到的 a_horse_with_no_name DEC， 并不一定意味着 12 月。这取决于您NLS_DATE_LANGUAGE和NLS_DATE_FORMAT设置。为了确保在任何语言环境中工作的比较，你可以使用日期时间格式模型MM代替
• 95 年是不准确的。你知道你的意思是 1995 年，但如果是 50 年，那是 1950 年还是 2050 年呢？明确的总是最好的

select employee_id
  from employee
 where employee_date_hired > to_date('31-12-1995','DD-MM-YYYY')

Date literals

日期文字

A date literal is part of the ANSI standard, which means you don't have to use an Oracle specific function. When using a literal you mustspecify your date in the format YYYY-MM-DDand you cannot include a time element.

日期文字是 ANSI 标准的一部分，这意味着您不必使用特定于 Oracle 的函数。使用文字时，您必须在格式中指定日期，YYYY-MM-DD并且不能包含时间元素。

select employee_id
  from employee
 where employee_date_hired > date '1995-12-31'

Remember that the Oracle date datatype includes a time elemement, so the date without a time portion is equivalent to 1995-12-31 00:00:00.

请记住，Oracle 日期数据类型包括时间元素，因此没有时间部分的日期等效于1995-12-31 00:00:00.

If you want to include a time portion then you'd have to use a timestamp literal, which takes the format YYYY-MM-DD HH24:MI:SS[.FF0-9]

如果要包含时间部分，则必须使用时间戳文字，其格式为 YYYY-MM-DD HH24:MI:SS[.FF0-9]

select employee_id
  from employee
 where employee_date_hired > timestamp '1995-12-31 12:31:02'

Further information

更多信息

NLS_DATE_LANGUAGEis derived from NLS_LANGUAGEand NLS_DATE_FORMATis derived from NLS_TERRITORY. These are set when you initially created the database but they can be altered by changing your inialization parameters file - only if really required - or at the session level by using the ALTER SESSIONsyntax. For instance:

NLS_DATE_LANGUAGE源自NLS_LANGUAGE并且NLS_DATE_FORMAT源自NLS_TERRITORY。这些是在您最初创建数据库时设置的，但可以通过更改初始化参数文件（仅在确实需要时）或在会话级别使用ALTER SESSION语法来更改它们。例如：

alter session set nls_date_format = 'DD.MM.YYYY HH24:MI:SS';

This means:

这意味着：

• DDnumeric day of the month, 1 - 31
• MMnumeric month of the year, 01 - 12 ( January is 01 )
• YYYY4 digit year - in my opinion this is alwaysbetter than a 2 digit year YYas there is no confusion with what century you're referring to.
• HH24hour of the day, 0 - 23
• MIminute of the hour, 0 - 59
• SSsecond of the minute, 0-59

• DD月份中的数字日期，1 - 31
• MM一年中的数字月份，01 - 12（一月是 01）
• YYYY4 位数年份 - 在我看来，这总是比 2 位数年份好，YY因为与您所指的世纪没有混淆。
• HH24一天中的小时，0 - 23
• MI一小时的分钟，0 - 59
• SS一分钟的秒，0-59

You can find out your current language and date language settings by querying V$NLS_PARAMETERSsand the full gamut of valid values by querying V$NLS_VALID_VALUES.

您可以通过查询找到您当前的语言和日期语言设置，V$NLS_PARAMETERSs并通过查询找到所有有效值V$NLS_VALID_VALUES。

Further reading

进一步阅读

• Format models

• 格式模型

---

---

Incidentally, if you want the count(*)you need to group by employee_id

顺便说一句，如果你想要count(*)你需要分组employee_id

select employee_id, count(*)
  from employee
 where employee_date_hired > date '1995-12-31'
 group by employee_id

This gives you the count peremployee_id.

这为您提供了per的计数employee_id。

回答by akshay gulawane

Conclusion,

结论，

to_charworks in its own way

to_char以自己的方式工作

So,

所以，

Always use this format YYYY-MM-DDfor comparison instead of MM-DD-YYor DD-MM-YYYYor any other format

始终使用这种格式YYYY-MM-DD进行比较，而不是MM-DD-YY或DD-MM-YYYY或任何其他格式

回答by Giovanny Farto M.

You can use trunc and to_date as follows:

您可以使用 trunc 和 to_date 如下：

select TO_CHAR (g.FECHA, 'DD-MM-YYYY HH24:MI:SS') fecha_salida, g.NUMERO_GUIA, g.BOD_ORIGEN, g.TIPO_GUIA, dg.DOC_NUMERO, dg.* 
from ils_det_guia dg, ils_guia g
where dg.NUMERO_GUIA = g.NUMERO_GUIA and dg.TIPO_GUIA = g.TIPO_GUIA and dg.BOD_ORIGEN = g.BOD_ORIGEN
and dg.LAB_CODIGO = 56 
and trunc(g.FECHA) > to_date('01/02/15','DD/MM/YY')
order by g.FECHA;

回答by viduka

from your query:

从您的查询：

Select employee_id, count(*) From Employee 
Where to_char(employee_date_hired, 'DD-MON-YY') > '31-DEC-95' 

i think its not to display the number of employees that are hired after June 20, 1994. if you want show number of employees, you can use:

我认为它不显示 1994 年 6 月 20 日之后雇用的员工人数。如果您想显示员工人数，可以使用：

Select count(*) From Employee 
Where to_char(employee_date_hired, 'YYYMMMDDD') > 19940620 

I think for best practice to compare dates you can use:

我认为最好的做法是比较您可以使用的日期：

employee_date_hired > TO_DATE('20-06-1994', 'DD-MM-YYYY');
or
to_char(employee_date_hired, 'YYYMMMDDD') > 19940620;

回答by MVB

Single quote must be there, since date converted to character.

单引号必须在那里，因为日期转换为字符。

Select employee_id, count(*)
From Employee
Where to_char(employee_date_hired, 'DD-MON-YY') > '31-DEC-95';