Print only those Months that have 31 days

user_15564506 0
Viewed 76

I'm new to SQL so I am practicing different problems and unfortunately I'm stuck with one of them.

Date Column

| HIREDATE  |
| --------- |
| 17-NOV-81 |
| 01-MAY-81 |
| 09-JUN-81 |
| 02-APR-81 |
| 19-APR-87 |
| 03-DEC-81 |
| 17-DEC-80 |
| 20-FEB-81 |
| 22-FEB-81 |
| 28-SEP-81 |
| 08-SEP-81 |
| 23-MAY-87 |
| 03-DEC-81 |
| 23-JAN-82 |

Problem

Here I am trying to list only those dates in which their specific months has 31 days I am using Last_Day() function but it shows Error: ORA-00932: inconsistent datatypes: expected DATE got NUMBER.

This is the Query I am using:

SELECT ENAME, HIREDATE FROM scott.emp WHERE LAST_DAY(HIREDATE)=31;

I also tried to Extract Day from date but still its not working and showing the same Error.

SELECT ENAME, HIREDATE FROM scott.emp WHERE LAST_DAY(EXTRACT(DAY FROM HIREDATE))=31

As I am a beginner so I am using Oracle Live SQL

Is there any other way of Listing only those dates that has just 31 days in Oracle SQL?

oracle sql
2 Answers

Something like this:

SELECT ENAME, HIREDATE FROM scott.emp WHERE EXTRACT(DAY FROM LAST_DAY(HIREDATE)) = 31;
user_81032630

Alternatively, you could use TO_CHAR function with appropriate format mask:

SQL> select ename, hiredate from emp
  2  where to_char(last_day(hiredate), 'dd') = '31';

ENAME      HIREDATE
---------- ----------
SMITH      17.12.1980
BLAKE      01.05.1981
SCOTT      09.12.1982
ADAMS      12.01.1983
JAMES      03.12.1981
FORD       03.12.1981
MILLER     23.01.1982

7 rows selected.

SQL>
user_90979060
Related