Date Comparison in Oracle SQL

Question

I try to get it to display the number of employees who are hired after June 20, 1994, but I get the error "Invalid JUN ID". Please help, thanks!

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

Answer 1

31-DEC-95 not a string and is not 20-JUN-94 . These are numbers with the addition of additional material at the end. It should be '31-DEC-95' or '20-JUN-94' - mark single quote ' . This will allow you to perform string comparisons.

However, you are not comparing strings; you are doing a date comparison. You must convert the string to a date. Either using the built-in TO_DATE() function, or date literal .

TO_DATE ()

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

This method has some unnecessary errors

• As noted in the comments of a_horse_with_no_name, DEC does not necessarily mean December. It depends on the settings of NLS_DATE_LANGUAGE and NLS_DATE_FORMAT . To make sure that your comparison with work in any locale you can use the model of the format datetime MM instead
• The year '95 is inaccurate. You know what you mean 1995, but what if it was 50 years old, is it 1950 or 2050? It is always better to be explicit.

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

Date literals

The date literal is part of the ANSI standard, which means that you do not need to use the special Oracle 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 data type includes a time element, so a date without a time part is equivalent to 1995-12-31 00:00:00 .

If you want to enable the temporary part, you will have to use the timestamp literal, which accepts 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' 

Additional Information

NLS_DATE_LANGUAGE obtained from NLS_LANGUAGE and NLS_DATE_FORMAT derived from NLS_TERRITORY . They are installed when you initially created the database, but you can change them by changing the initialization parameters file - only if it is really necessary - or at the session level using ALTER SESSION . For example:

 alter session set nls_date_format = 'DD.MM.YYYY HH24:MI:SS'; 

It means:

• DD numeric day of the month, 1 - 31
• MM numeric month of the year, 01 - 12 (January 01)
• YYYY 4-digit year - in my opinion, it is always better than a two-digit year YY , because there is no confusion with what century you mean.
• HH24 hour of the day, 0 - 23
• MI minute of an hour, 0 - 59
• SS second minutes, 0-59

You can find out your current language and date language settings by V$NLS_PARAMETERSs and the full gamut of valid values ​​by V$NLS_VALID_VALUES .

Further reading

• Format Models

---

By the way, if you want 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 an account for employee_id .

Answer 2

Output

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

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; 

Answer 4

from your request:

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

I think that it does not reflect the number of employees hired after June 20, 1994. If you want to show the number of employees, you can use:

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

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

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

Answer 5

A single quote must be there, as the date is converted to a character.

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