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:
Post a Comment