Extract year, month, day from a date : EXTRACT « Date Timestamp Functions « Oracle PL/SQL Tutorial

Oracle

Select *
FROM table_name
WHERE to_char(TRIP_TIMESTAMP, ‘Month’) = to_char(sysdate, ‘Month’)

select * from SL_GAME

delete from SL_GAME where game_id = 10

commit;

Select *
FROM table_name
WHERE to_char(TRIP_TIMESTAMP, ‘Month’) = to_char(add_months(TRUNC(SYSDATE) + 1, 11), ‘Month’)

— Return and comparision of November month

Select *
FROM table_name
WHERE to_char(TRIP_TIMESTAMP, ‘Month’) = to_char(add_months(TRUNC(SYSDATE) + 1, 2), ‘Month’)

— Return and comparision of Febrary month

extract_datetime::=

Description of extract_datetime.gif follows

  • If YEAR or MONTH is requested, then expr must evaluate to an expression of datatype DATE, TIMESTAMP,TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, or INTERVAL YEAR TO MONTH.
  • If DAY is requested, then expr must evaluate to an expression of datatype DATE, TIMESTAMP, TIMESTAMP WITHTIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, or INTERVAL DAY TO SECOND.
  • If HOUR, MINUTE, or SECOND is requested, then expr must evaluate to an expression of datatype TIMESTAMP,TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, or INTERVAL DAY TO SECOND. DATE is not valid here, because Oracle Database treats it as ANSI DATE datatype, which has no time fields.
  • If TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_ABBR, TIMEZONE_REGION, or TIMEZONE_OFFSET is requested, then expr must evaluate to an expression of datatype TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCALTIME ZONE.
SELECT EXTRACT(month FROM order_date) "Month",
  COUNT(order_date) "No. of Orders"
  FROM orders
  GROUP BY EXTRACT(month FROM order_date)
  ORDER BY "No. of Orders" DESC;

     Month No. of Orders
---------- -------------
        11            15
         7            14
         6            14
         3            11
         5            10
         9             9
         2             9
         8             7
        10             6
         1             5
        12             4
         4             1
SELECT EXTRACT(YEAR FROM DATE '1998-03-07') FROM DUAL;

EXTRACT(YEARFROMDATE'1998-03-07')
---------------------------------
                             1998

SELECT last_name, employee_id, hire_date
   FROM employees
   WHERE EXTRACT(YEAR FROM
   TO_DATE(hire_date, 'DD-MON-RR')) > 1998
   ORDER BY hire_date;

LAST_NAME                 EMPLOYEE_ID HIRE_DATE
------------------------- ----------- ---------
Landry                            127 14-JAN-99
Lorentz                           107 07-FEB-99
Cabrio                            187 07-FEB-99

IN SQL

SQL>
SQL> select bdate
  2  ,      extract(year  from bdate) as year_of_birth
  3  ,      extract(month from bdate) as month_of_birth
  4  ,      extract(day   from bdate) as day_of_birth
  5  from   emp
  6  where  ename = 'Peter';

BDATE      YEAR_OF_BIRTH MONTH_OF_BIRTH DAY_OF_BIRTH
---------- ------------- -------------- ------------
17-11-1952          1952             11           17

select to_char(sysdate, 'Month') from dual
select to_char(to_date('15-11-2010', 'DD-MM-YYYY'), 'Month') from dual
select to_char(sysdate,'dd') from dual; -> 08 (date)
select to_char(sysdate,'mm') from dual; -> 02 (month in number)
select to_char(sysdate,'yyyy') from dual; -> 2013 (Full year)


Advertisements