Monday, February 1, 2016

How to get or extract year,month,day fom date or timestamp field of a table in oracle


How to extract year,month,day  from timestamp or date field of a table in oracle:
 Here service start date is the column name:
 
   SELECT EXTRACT(YEAR FROM service_start_date) FROM apcadm.apc_info_service where service_start_date IS NOT NULL

2009
2010
2011
2012
2010
   
   SELECT min(EXTRACT(YEAR FROM service_start_date)) FROM apcadm.apc_info_service where service_start_date IS NOT NULL 


1998
getting a count wise report:

 SELECT count(info_fk), EXTRACT(YEAR FROM service_start_date) FROM   apcadm.apc_info_service where service_start_date IS NOT NULL
   group by EXTRACT(YEAR FROM service_start_date)
   order by 2;

Same way we can extract the month number and day of the month:

 SELECT EXTRACT(month FROM service_start_date) FROM apcadm.apc_info_service where service_start_date IS NOT NULL
  
   SELECT EXTRACT(day FROM service_start_date) FROM apcadm.apc_info_service where service_start_date IS NOT NULL

No comments: