问题描述

我试图让它显示 1994 年 6 月 20 日之后雇用的员工数量，

I'm trying to get it to display the number of employees that are hired after June 20, 1994,

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

但我收到一条错误消息

JUN"无效标识符.

请帮忙，谢谢！

推荐答案

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

31-DEC-95 isn'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.

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

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.

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

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

This method has a few unnecessary pitfalls

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

• As a_horse_with_no_name noted in the comments, DEC, doesn't necessarily mean December. It depends on your NLS_DATE_LANGUAGE and NLS_DATE_FORMAT settings. To ensure that your comparison with work in any locale you can use the datetime format model MM instead
• 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

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

日期文字

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

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 must specify your date in the format YYYY-MM-DD and you cannot include a time element.

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

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

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.

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

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]

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

更多信息

NLS_DATE_LANGUAGE 源自 NLS_LANGUAGE 和 NLS_DATE_FORMAT 源自 NLS_TERRITORY.这些是在您最初创建数据库时设置的，但可以通过更改初始化参数文件(仅在确实需要时)或在会话级别使用 ALTER SESSION 语法.例如:

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

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