In my Oracle database I have a table employees
with thousand of records.
It has columns:
employee_id
first_name
last_name
email
salary
hire_date
I want to show employees who joined in each year with a hire_date
on the 23
th day of month March
.
However, when using below SELECT statement it shows only empty records.
SELECT FIRST_NAME,LAST_NAME,EMAIL FROM EMPLOYEES WHERE to_char(hire_date, 'mon')='dec' AND to_char(hire_date, 'DD')='23';
tryng to display a records but it is no showing.what should be aproper format for displaing date and month of particular records.
Advertisement
Answer
First of all, in your question you say you are looking for employees on the 23rd of March, but in the where clause of your example query, you are searching for employees hired on the 23rd of December.
To prevent any differentiating spellings of months in different languages and compare using one filter in your where clause instead of two, you can use a query like this one.
SELECT FIRST_NAME, LAST_NAME, EMAIL FROM EMPLOYEES WHERE TO_CHAR (hire_date, 'MMDD') = '0323';