Comparing Dates in Oracle SQL

Comparing Dates in Oracle SQL

31-DEC-95 isnt a string, nor is 20-JUN-94. Theyre 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, youre not doing a string comparison; youre 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, doesnt 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? Its 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 dont 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 elemement, 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 youd 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 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 youre 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.

Conclusion,

to_char works in its own way

So,

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

Comparing Dates in Oracle SQL

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;