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!
> <
or BETWEEN '' AND ''
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
.
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
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;
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;
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';
Success story sharing
DEC
is not necessarily always a valid month. It's usually better to use numbers instead of namestimestamp
literal instead of adate
literal:timestamp '2015-01-30 19:42:04'
(because in ANSI SQL a thedate
data type doesn't have a time, only thetimestamp
data type does).DATE 2016-04-01
means2016-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.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