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;
But I get an error saying
"JUN' invalid identifier.
Please help, thanks!
5 Answers
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.
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()
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 yourNLS_DATE_LANGUAGEandNLS_DATE_FORMATsettings. To ensure that your comparison will 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
select employee_id
from employee
where employee_date_hired > to_date('31-12-1995','DD-MM-YYYY')
