Oracle SQL Date column Scripts

-- Last Month            

select to_char(HIRE_DATE,'mm-dd-yyyy')

from EMP          

where HIRE_DATE between  to_date(trunc(trunc(sysdate,'MM')-1,'MM'),'dd-mm-yy') and  to_date(trunc(sysdate,'MM')-1,'dd-mm-yy')            

            

-- Last 3 Months            

select to_char(HIRE_DATE,'mm-dd-yyyy')

from EMP          

where HIRE_DATE between  to_date(trunc(add_months(sysdate,-3),'MM'),'dd-mm-yy') and  to_date(trunc(sysdate,'MM')-1,'dd-mm-yy')            

            

-- Last 6 Months            

select to_char(HIRE_DATE,'mm-dd-yyyy') from

EMP            

where HIRE_DATE between  to_date(trunc(add_months(sysdate,-6),'MM'),'dd-mm-yy') and  to_date(trunc(sysdate,'MM')-1,'dd-mm-yy')         

            

--  Add Month and To_char         

SELECT sysdate,add_months(sysdate,-3),to_char(add_months(sysdate,-12*6),'Day')

FROM dual         

            

--  First Date of Current Month          

select to_char(HIRE_DATE,'mm-dd-yyyy')

from EMP          

where HIRE_DATE = to_date(trunc(sysdate,'MM'),'dd-mm-yy')         

            

--  Last Date of current Month           

select to_char(HIRE_DATE,'mm-dd-yyyy')

from EMP          

where HIRE_DATE = to_date(trunc(add_months(sysdate,1),'MM')-1,'dd-mm-yy')