Saturday, August 13, 2016

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')          


No comments:

Post a Comment

Best Blogger TipsGet Flower Effect