I want to filter out employees that have start_date of Mar-07 I tried below query for the same but no rows got selected .Is my approach wrong?

SELECT * 
FROM EMP_TABLE
WHERE START_DATE LIKE '%Mar-07%';

Also my table structure is like this:

Name        Null? Type
M_ID              VARCHAR2(4)
M_NAME            VARCHAR2(20)
DEPT_ID           CHAR(4)
START_DATE        DATE
END_DATE          DATE
SALARY            NUMBER(6)

CodePudding user response：

Your current query:

SELECT * FROM EMP_TABLE WHERE START_DATE LIKE '%Mar-07%';

relies on implicit conversion of every date to a string using your session NLS settings, and then string comparison. While that could work, it looks like maybe your NLS settings don't seem to match that format - PL/SQL Developer might be formatting the dates itself - or you're actually seeing MAR-07 and aren't matching the case properly.

You can see your NLS settings by selecting TO_CHAR(START_DATE), or by querying the nls_session_parameters view.

In any case, it would be better to search for a range of dates, rather than converting to strings:

SELECT * FROM EMP_TABLE
WHERE START_DATE >= DATE '2007-03-01'
AND START_DATE < DATE '2007-04-01'

Note that this will only find dates in 2007, while your original would include 1907, 1807 etc. - but that's probably what you want really.