- get the year, day, quarter
Select to_char (Date, 'YYYMMDD') DATE_ID, TO_CHAR (Date, 'YYYY') || 'Year' || To_Char (Date, 'MM') || 'Moon' || To_Char (Date, 'DD') | | 'Day' Date_Name, TO_CHAR (Date, 'YYYYMM') MONTH_ID, TO_CHAR (Date, 'YYYY') || 'Year' || To_Char (Date, 'MM') || 'Month_name,' Q '| | To_char (date, 'Q.YYY') Quarterid, TO_CHAR (Date, 'YYYY') || 'Years || To_Char (Date,' Q ') ||' Quarter 'Quarterid_name, To_Char (Date,' YYYY) ') Year_ID, TO_CHAR (Date,' YYYY ') ||' Year 'Year_name from (SELECT TO_DATE (' 2000-01-01 ') (ROWNUM-1) Date from User_Objects Where Rownum <367 and to_date ('2000-01-01', 'YYYY-MM-DD') (ROWNUM-1) - Get quarterly and month correspondence Select distinct to_char (date, 'q') quarter, to_char (to_date ('2001-01-01', 'YYYY-MM-DD') (ROWNUM-1), 'YYYYMM') Date from (SELECT TO_DATE 2001-01 ',' YYYY-mm ') (ROWNUM-1) Date from User_Objects Where Rownum <367 and to_date (' 2001-01-01 ',' YYYY-MM-DD ') (ROWNUM-1) < TO_DATE ('2002-01-01', 'YYYY-MM-DD'); - get the number of days in the year SELECT TO_CHAR (TO_DATE ('2000-01-01', 'YYYY-MM-DD') (ROWNUM-1), 'YYYY-MM-DD') Date from User_Objects Where Rownum <367 and to_date ('2000-01 -01 ',' YYYY-MM-DD ') (ROWNUM-1)