suppose enp table has 20 records .there i want to retrieve records i.e employees having date of birth as 1 st day of the month or last day of month

Try this
select * from employee where to_char(dateofbirth,'dd') in ('01',to_char(last_day(dateofbirth),'dd'));

Before posting these kind of questions please post what / how you have tried from your side to solve it and if you are facing any problem. That will be more interactive.

select * from employee where to_char(dateofbirth,'dd') in ('01',to_char(last_day(dateofbirth),'dd'));

Hi Jayanth..
Please write a procedure for your requirement like the 1 st day of month. The procedure should contain the variable @temp(@temp stores the data for the day of the query, like @temp=select day(DOB) from emp). Now you can check the condition)

I think it may help you but you check the result by using trial end error method.


Thank You.


Regards,

Sharavanan S

SELECT * FROM emp where (TRUNC(hire_date) = TRUNC(hire_date,'MM') OR TRUNC(hire_date) = TRUNC(LAST_DAY(hire_date)))