Introduction

This is a very common situation when one user wants to retrieve Date or Month or Year from a date field.
This is a very easy and there are various ways to retrieve Date or Month or Year from a Data filed column. Please find some example as given below section.

SQL Examples

There are various ways to solve a problem and this problem also we can solve using various ways. Please find few SQL Query to retrieve Date/Month/Year from Date Column.

SELECT TO_CHAR(COLUMN_NAME, ‘DD’) FROM TABLE_NAME;

SELECT TO_CHAR(STARTDATE, ‘DD’) FROM CASEHEADER;

RESULT

SELECT TO_CHAR(STARTDATE, ‘MM’) FROM CASEHEADER;

RESULT

SELECT TO_CHAR(STARTDATE, ‘YY’) FROM CASEHEADER;

RESULT

SELECT TO_CHAR(STARTDATE, ‘YYYY’) FROM CASEHEADER;

RESULT

---

Second Way

This is a second way of retrieving Date or Month or Year from a date column fields. This is more readable then the previous one. We executed on Oracle and it produce the proper results.

SELECT EXTRACT(MONTH FROM STARTDATE) “MONTH” FROM CASEHEADER;

STARTDATE: This is a column name used to retrieve Month.

CASEHEADER: This is a table name.

RESULT

SELECT EXTRACT(DAY FROM STARTDATE) “DAY” FROM CASEHEADER;

RESULT

SELECT EXTRACT(YEAR FROM STARTDATE) “YEAR” FROM CASEHEADER;

RESULT

Also, We can use this statement in the WHERE condition as well to retrieve specific records. Suppose we have millions of records and most of the records start from the first of the month but few are not started from first of the month then we use this kinds of statement in the WHERE condition.

SELECT * FROM CASEHEADER WHERE EXTRACT(DAY FROM STARTDATE) <> ’01’;

Result

SELECT last_name, employee_id, hire_date FROM employees WHERE EXTRACT(YEAR FROM TO_DATE(hire_date, ‘DD-MON-RR’)) > 1998 ORDER BY hire_date;

LAST_NAME	EMPLOYEE_ID	HIRE_DATE
ABC	127	14-JAN-99
ABCDF	107	07-FEB-99
ABCFDSD	187	07-FEB-99