在Oracle SQL中比较日期 [英] Comparing Dates in Oracle SQL

问题描述

我正试图显示1994年6月20日之后的雇员人数， 但是我收到一个错误消息，说"JUN"无效的标识符.请帮忙，谢谢！

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!

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

推荐答案

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 设置.为确保与在任何语言环境下的工作进行比较，您可以使用 datetime格式模型 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 和

Further information

NLS_DATE_LANGUAGE is derived from NLS_LANGUAGE and NLS_DATE_FORMAT is 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 SESSION syntax. For instance:

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

这意味着:

• DD数字月份，1-31
• MM一年中的数字月份，即01-12(一月是01)
• YYYY 4位数字的年份-我认为这总是比2位数字的年份YY好，因为与您所指的世纪没有混淆.
• HH24每天的小时，0-23
• MI每小时的分钟，0-59
• SS分钟，0-59

• DD numeric day of the month, 1 - 31
• MM numeric month of the year, 01 - 12 ( January is 01 )
• YYYY 4 digit year - in my opinion this is always better than a 2 digit year YY as there is no confusion with what century you're referring to.
• HH24 hour of the day, 0 - 23
• MI minute of the hour, 0 - 59
• SS second of the minute, 0-59

您可以通过查询V$NLS_PARAMETERSs来找到当前的语言和日期语言设置，并可以通过查询V$NLS_VALID_VALUES来找到有效值的全部范围.

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

• 格式化模型

顺便说一句，如果您想要count(*)，则需要按employee_id

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

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

这将为您提供每个 employee_id的计数.

This gives you the count per employee_id.

这篇关于在Oracle SQL中比较日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！