Comparing Dates in Oracle SQL
SqlOracleDate ComparisonSql Problem Overview
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!
Sql Solutions
Solution 1 - Sql
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_LANGUAGE
andNLS_DATE_FORMAT
settings. To ensure that your comparison will work in any locale you can use the datetime format modelMM
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 - 31MM
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 yearYY
as there is no confusion with what century you're referring to.HH24
hour of the day, 0 - 23MI
minute of the hour, 0 - 59SS
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
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
.
Solution 2 - Sql
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
Solution 3 - 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;
Solution 4 - Sql
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;
Solution 5 - Sql
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';