How to Correctly Compare Dates in Oracle SQL to Count Employees Hired After a Specific Date?

Oracle SQL date comparison and employee joining date count

Question:

I need to count the number of employees who joined after June 20, 1994, but I encountered an error:

<code class="language-sql">Select employee_id, count(*)
From Employee
Where to_char(employee_date_hired, 'DD-MON-YY') > '31-DEC-95'; </code>

Error:

"JUN' invalid identifier."

Answer:

String and date comparison:

The error occurs when comparing a string ('31-DEC-95') to a date value (employee_date_hired). To do date comparison, you need to convert the string to a date using the TO_DATE() function, or use a date literal.

1. TO_DATE() function:

<code class="language-sql">select employee_id
from employee
where employee_date_hired > to_date('31-DEC-95','DD-MON-YY')</code>

2. Date literal:

If you want consistent behavior across different locales and precise date comparisons, you can use date literals.

<code class="language-sql">select employee_id
from employee
where employee_date_hired > date '1995-12-31'</code>

Tips:

• Specify the complete year (YYYY) to avoid confusion.
• Use the NLS_DATE_LANGUAGE and NLS_DATE_FORMAT settings to customize date formats.
• For count query, grouping by employee_id is required:

<code class="language-sql">select employee_id, count(*)
from employee
where employee_date_hired > date '1995-12-31'
group by employee_id</code>

The above is the detailed content of How to Correctly Compare Dates in Oracle SQL to Count Employees Hired After a Specific Date?. For more information, please follow other related articles on the PHP Chinese website!