extract year month day
select date, extract(year from date) as yearpart, extract(month from date) as monthpart, extract(day from date) as daypart
result:
Date yearpart monthpart daypart
04/15/2011 2011 4 15
extract hour minute second
select time, extract(hour from time) as hourpart, extract(minute from time) as minutepart, extract(second from time) as secondpart
result:
Time hourpart minutepart secondpart
13:54:49 13 54 49.65
date decomposition
select * from sys_calendar.calendar where calendar_date= date '2011-04-18'
result:
calendar_date day_of_week day_of_month day_of_year day_of_calendar weekday_of_month week_of_month week_of_year week_of_calendar month_of_quarter month_of_year month_of_calendar quarter_of_year quarter_of_calendar year_of_calendar
04/18/2011 2 18 108 40650 3 3 16 5807 1 4 1336 2 446 2011