Comparing Dates in Oracle SQL

Question

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!

Infelicitous

Note also that you can use either > < or BETWEEN '' AND '' – Lindner

Answer 1

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 your NLS_DATE_LANGUAGE and NLS_DATE_FORMAT settings. To ensure that your comparison will 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')

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'

Remember that the Oracle date datatype includes a time element, so the date without a time portion is equivalent to 1995-12-31 00:00:00.

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'

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 initialization 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_PARAMETERSs and the full gamut of valid values by querying V$NLS_VALID_VALUES.

Further reading

• Format models

---

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

This gives you the count per employee_id.

Lamarlamarck

+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 – Clear

@a_horse_with_no_name, thanks for the point. I've updated with your suggestion. – Lamarlamarck

You can specify a time with an ANSI literal - you just need to specify a timestamp literal instead of a date literal: timestamp '2015-01-30 19:42:04' (because in ANSI SQL a the date data type doesn't have a time, only the timestamp data type does). – Clear

The ANSI date literals is really a concise way comparing having to type TO_DATE and Date-Format every time. Good for LAZY developers like me. One thing to Notice is the DATE 2016-04-01 means 2016-04-01 00:00:00 really. And I think this syntax works since Oracle 9i as this is where ANSI-SQL syntax was introduced into Oracle. – Shingle

My thinking has evolved significantly in the last 4 years @Leon :-)' I've updated the answer. I had mentioned that a date literal didn't include a time element but I've called this out more explicitly as you've stated. 9i extended support ended almost 6 years ago... and was released 14 years ago. It shouldn't be relevant any more for the vast majority of users. – Lamarlamarck

Is alter session equivalent to define somehow? – Contract

No, it's very different @Alireza. define is a SQL*Plus command that substitutes whatever you've defined into all substitution variables with that name. ALTER SESSION is a statement that allows you to modify some database parameters or settings for the duration of that session – Lamarlamarck

Can you try do the same example but comparing with sysdate ? :) – Five

Because you're comparing comparable data types (in this case date -> date, timestamp -> timestamp or date -> timestamp) it's very simple @delive. SYSDATE is a date so replace the appropriate date or timestamp with SYSDATE. – Lamarlamarck

OMG I've been querying Oracle for 20+ years and always hated the TO_DATE syntax. I'd never seen the literal date until today - it's so clear and simple! I will not shy from Oracle date filters anymore - thank you! – Shifty

Answer 2

to_char works in its own way.

Always use this format YYYY-MM-DD for comparison instead of MM-DD-YY or DD-MM-YYYY or any other format.

Laural

Answer 3

You can use trunc and to_date as follows:

select TO_CHAR (g.FECHA, 'DD-MM-YYYY HH24:MI:SS') fecha_salida, g.NUMERO_GUIA, g.BOD_ORIGEN, g.TIPO_GUIA, dg.DOC_NUMERO, dg.* 
from ils_det_guia dg, ils_guia g
where dg.NUMERO_GUIA = g.NUMERO_GUIA and dg.TIPO_GUIA = g.TIPO_GUIA and dg.BOD_ORIGEN = g.BOD_ORIGEN
and dg.LAB_CODIGO = 56 
and trunc(g.FECHA) > to_date('01/02/15','DD/MM/YY')
order by g.FECHA;

Trike

Answer 4

from your query:

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

i think its not to display the number of employees that are hired after June 20, 1994. if you want show number of employees, you can use:

Select count(*) From Employee 
Where to_char(employee_date_hired, 'YYYMMMDDD') > 19940620 

I think for best practice to compare dates you can use:

employee_date_hired > TO_DATE('20-06-1994', 'DD-MM-YYYY');
or
to_char(employee_date_hired, 'YYYMMMDDD') > 19940620;

Penthouse

Answer 5

Single quote must be there, since date converted to character.

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

Glyn

This SQL uses an implicit date format, it will not always work. – Honeyhoneybee

It's only working fine for your specific NLS_* settings, it may not work on other clients or servers. The accepted answer explains why an explicit date format is important. – Honeyhoneybee

This method is comparing strings, not dates. The second string starts with a "3", so the compare works like "alphabetical order". Interestingly, this type of compare actually works ( sort-of by accident ) if you use a format like YYYY-MM-DD. But of course, it's better to compare dates to dates... – Accouterment