Take the 2-minute tour ×

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; 
share|improve this question
add comment

1 Answer

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 inverted commas, '. 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 using the built-in to_date function.

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

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.

If you want to avoid this pitfall replace DEC with 12 and use the datetime format model MM to indicate this:

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

Oracle does support ANSI date literals, though I prefer not to use them as I find the syntax less clear. When using a literal you must specify your date in the format YYYY-MM-DD and you cannot include a time portion.

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

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';

This means:

  • 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

You can find out your current language and date language settings by querying v$nls_parameters and the full gamut of valid values by querying v$nls_valid_values.

Further reading:

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

select employee_id, count(*)
  from employee
 group by employee_id

This gives you the count per employee_id.

share|improve this answer
+1 for using a format mask in to_date(). Note that this can still fail on a different environments due to different language settings. DEC is not necessarily always a valid month. It's usually better to use numbers instead of names –  a_horse_with_no_name Apr 16 '12 at 16:56
@a_horse_with_no_name, thanks for the point. I've updated with your suggestion. –  Ben Apr 16 '12 at 17:01
Thanks so much for the detailed explanation! –  user1336830 Apr 16 '12 at 19:06
+1 this really helped. Thanks. –  Radu Murzea Aug 30 '13 at 13:43
add comment

Your Answer


