Просмотр исходного текста страницы Oracle PL/SQL/Date Timezone/Date Format

==Change the original date format in the TO_CHAR function==



   
  <!-- start source code -->
   
    <source lang="sql">

SQL>
SQL> -- Change the original date format in the TO_CHAR function
SQL>
SQL> SELECT TO_CHAR(LAST_DAY(TO_DATE("23SEP2006","ddMONyyyy")),"Month dd, yyyy") FROM dual;
TO_CHAR(LAST_DAY(T
------------------
September 30, 2006
SQL>
           
       </source>
    
   


==Combine to_char and trunc with date value==



   
  <!-- start source code -->
   
    <source lang="sql">
 
SQL>
SQL> select to_char( trunc( sysdate, "HH" ), "DD-MON-YY HH24:MI:SS" ) "The Current Hour"
  2  from dual
  3  /
The Current Hour
------------------
16-JUN-08 17:00:00
1 row selected.
SQL>
SQL> --
 </source>
    
   


==Converting a DATE to Another Language==



   
  <!-- start source code -->
   
    <source lang="sql">
 
SQL>
SQL> SELECT TO_CHAR(SYSDATE,"MONTH DD YY","NLS_DATE_LANGUAGE=german")
  2        "German Date" from dual;
German Date
---------------
JUNI      16 08
1 row selected.
SQL>
SQL>
SQL>
SQL> --
 </source>
    
   


==format parameter options available==



   
  <!-- start source code -->
   
    <source lang="sql">
 
Format Element     Description                                        Example
AD                 AD indicator                                       TO_CHAR (SYSDATE,"YYYY AD")
AM                 Meridian indicator (am/pm)                         TO_CHAR (SYSDATE,"HH:MI:SS AM")
BC                 BC indicator (Before Common era/Before Christ)     TO_CHAR (SYSDATE,"YYYY BC")
D                  Day of the week (from 1 to 7)                      TO_CHAR (SYSDATE,"D")
DAY                Name of the day                                    TO_CHAR (SYSDATE,"DAY")
DD                 Day of the month (from 1 to 31)                    TO_CHAR (SYSDATE,"DD")
DDD                Day of the year (from 1 to 366)                    TO_CHAR (SYSDATE,"DDD")
DY                 Abbreviated name of the day                        TO_CHAR (SYSDATE,"DY")
HH                 Hour of the day (from 1 to 12)                     TO_CHAR (SYSDATE,"HH")
HH12               Hour of the day (from 1 to 12)                     TO_CHAR (SYSDATE,"HH12")
HH24               Hour of the day (from 0 to 23)                     TO_CHAR (SYSDATE,"HH24")
MI                 Minute (from 0 to 59)                              TO_CHAR (SYSDATE,"MI")
MM                 Month (from 01 to 12)                              TO_CHAR (SYSDATE,"MM")
MON                Abbreviated name of the month                      TO_CHAR (SYSDATE,"MON")
MONTH              Name of the month                                  TO_CHAR (SYSDATE,"MONTH")
PM                 Meridian indicator (am/pm)                         TO_CHAR (SYSDATE,"PM")
RM                 Roman numeral month (from I to XII)                TO_CHAR (SYSDATE,"RM")
RR                 Calculates full year given two digits              TO_CHAR (SYSDATE,"RR")
SS                 Second (from 0 to 59)                              TO_CHAR (SYSDATE,"SS") 

select
   TO_CHAR(SYSDATE,"DD-DAY-MONTH-YEAR") LONG_DATE
FROM dual;
--
 </source>
    
   


==Get the number of days in a month with to_char==



   
  <!-- start source code -->
   
    <source lang="sql">
 
SQL>
SQL> create table ord(
  2           order_no               integer          primary key
  3          ,cust_no                integer
  4          ,order_date             date not null
  5          ,total_order_price      number(7,2)
  6          ,deliver_date           date
  7          ,deliver_time           varchar2(7)
  8          ,payment_method         varchar2(2)
  9          ,emp_no                 number(3,0)
 10          ,deliver_name           varchar2(35)
 11          ,gift_message           varchar2(100)
 12  );
Table created.
SQL>
SQL>
SQL> insert into ord(order_no,cust_no,order_date,total_order_price,deliver_date,deliver_time,payment_method,emp_no,deliver_name,gift_message)
  2           values(1,1,"14-Feb-2002", 23.00, "14-Feb-2002", "12 noon", "CA",1, null, "Gift for wife");
1 row created.
SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time ,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(2,1,"14-Feb-2003", 510.98, "14-feb-2003", "5 pm", "NY",7, "Rose Ted", "Happy Valentines Day to Mother");
1 row created.
SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(3, 2,"14-Feb-2004", 315.99, "14-feb-2004", "3 pm", "VS",2, "Ani Forest", "Happy Valentines Day to Father");
1 row created.
SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(4, 2,"14-Feb-1999", 191.95, "14-feb-1999", "2 pm", "NJ",2, "O. John", "Happy Valentines Day");
1 row created.
SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message    )
  2           values(5, 6,"4-mar-2002", 101.95, "5-mar-2002", "2:30 pm", "MO"   , 2, "Cora", "Happy Birthday from John");
1 row created.
SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(6, 9,"7-apr-2003", 221.95, "7-apr-2003", "3 pm", "MA", 2, "Sake Keith", "Happy Birthday from Joe" );
1 row created.
SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(7, 9,"20-jun-2004", 315.95, "21-jun-2004", "12 noon", "BC", 2, "Jessica Li", "Happy Birthday from Jessica");
1 row created.
SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values (8, 12, "31-dec-1999", 135.95, "1-jan-2000", "12 noon", "DI",      3, "Larry", "Happy New Year from Lawrence");
1 row created.
SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values (9, 12, "26-dec-2003", 715.95, "2-jan-2004", "12 noon", "SK",7, "Did", "Happy Birthday from Nancy" );
1 row created.
SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(10, 4, sysdate-1, 119.95, sysdate+2, "6:30 pm", "VG",2, "P. Jing", "Happy Valentines Day to Jason");
1 row created.
SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(11, 2, sysdate, 310.00, sysdate+2, "3:30 pm", "DC",2, "C. Late", "Happy Birthday Day to Jack");
1 row created.
SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
  2           values(12, 7, sysdate-3, 121.95, sysdate-2, "1:30 pm", "AC",2, "W. Last", "Happy Birthday Day to You");
1 row created.
SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
  2           values(13, 7, sysdate, 211.95, sysdate-4, "4:30 pm", "CA",2, "J. Bond", "Thanks for hard working");
1 row created.
SQL>
SQL> select order_no, "Order placed on the " ||
  2         to_char(order_date, "fmddth") || " day of "||
  3         to_char(order_date, "fmMonth") || ", " ||
  4         to_char(order_date, "yyyy") as "Order date"
  5    from ord;
  ORDER_NO Order date
---------- -----------------------------------------------
         1 Order placed on the 14th day of February, 2002
         2 Order placed on the 14th day of February, 2003
         3 Order placed on the 14th day of February, 2004
         4 Order placed on the 14th day of February, 1999
         5 Order placed on the 4th day of March, 2002
         6 Order placed on the 7th day of April, 2003
         7 Order placed on the 20th day of June, 2004
         8 Order placed on the 31st day of December, 1999
         9 Order placed on the 26th day of December, 2003
        10 Order placed on the 15th day of June, 2008
        11 Order placed on the 16th day of June, 2008
        12 Order placed on the 13th day of June, 2008
        13 Order placed on the 16th day of June, 2008
13 rows selected.
SQL>
SQL> drop table ord;
Table dropped.
SQL> --
 </source>
    
   


==INITCAP(RTRIM(TO_CHAR(SYSDATE, "MONTH")))==



   
  <!-- start source code -->
   
    <source lang="sql">
 
SQL>
SQL> SELECT INITCAP(RTRIM(TO_CHAR(SYSDATE, "MONTH"))) ||
  2         " " ||
  3         LOWER(TO_CHAR(SYSDATE, "DDTH"))
  4  FROM DUAL;
INITCAP(RTRIM(
--------------
June 19th
SQL>
SQL>
 </source>
    
   


==initcap(rtrim(to_char(sysdate, "MONTH"))) ||" " || initcap(to_char(sysdate, "DDSPTH"))==



   
  <!-- start source code -->
   
    <source lang="sql">
 
SQL>
SQL> select initcap(rtrim(to_char(sysdate, "MONTH"))) ||" " || initcap(to_char(sysdate, "DDSPTH"))
  2  from dual;
INITCAP(RTRIM(TO_CHAR(SY
------------------------
June Nineteenth
SQL>
SQL>
 </source>
    
   


==INITCAP(RTRIM(TO_CHAR(SYSDATE, "MONTH"))) ||" " ||TO_CHAR(SYSDATE, "DDTH")==



   
  <!-- start source code -->
   
    <source lang="sql">
 
SQL>
SQL> SELECT INITCAP(RTRIM(TO_CHAR(SYSDATE, "MONTH"))) ||" " ||TO_CHAR(SYSDATE, "DDTH")
  2  FROM DUAL;
INITCAP(RTRIM(
--------------
June 19TH
 </source>
    
   


==select to_char( sysdate, "DD/MM/YY HH24:MI:SS" ) "Right Now"==



   
  <!-- start source code -->
   
    <source lang="sql">
 
SQL>
SQL>
SQL> select to_char( sysdate, "DD/MM/YY HH24:MI:SS" ) "Right Now"
  2  from dual
  3  /
Right Now
-----------------
16/06/08 17:28:47
1 row selected.
SQL> --
 </source>
    
   


==select to_char(sysdate,"DD-MON-YY HH24:MI:SS" ) "Right Now"==



   
  <!-- start source code -->
   
    <source lang="sql">
 
SQL>
SQL> select to_char(sysdate,"DD-MON-YY HH24:MI:SS" ) "Right Now"
  2  from dual
  3  /
Right Now
------------------
16-JUN-08 17:28:40
1 row selected.
SQL>
SQL>
SQL> --
 </source>
    
   


==SELECT TO_CHAR(SYSDATE, "MM-DD-YYYY HH24:MI:SS") NOW==



   
  <!-- start source code -->
   
    <source lang="sql">
 
SQL>
SQL> SELECT TO_CHAR(SYSDATE, "MM-DD-YYYY HH24:MI:SS") NOW
  2  FROM DUAL;
NOW
-------------------
06-19-2008 18:35:50
SQL>
 </source>
    
   


==to_char(LAST_STOCK_DATE, "MON DD, YYYY HH24:MI")==



   
  <!-- start source code -->
   
    <source lang="sql">
 
SQL>
SQL> CREATE TABLE product (
  2       product_name     VARCHAR2(25),
  3       product_price    NUMBER(4,2),
  4       quantity_on_hand NUMBER(5,0),
  5       last_stock_date  DATE);
Table created.
SQL>
SQL> INSERT INTO product VALUES ("Small Widget", 99, 1, "15-JAN-03");
1 row created.
SQL> INSERT INTO product VALUES ("Medium Widget", 75, 1000, "15-JAN-02");
1 row created.
SQL> INSERT INTO product VALUES ("Product Number", 50, 100, "15-JAN-03");
1 row created.
SQL> INSERT INTO product VALUES ("Round Church Station", 25, 10000, null);
1 row created.
SQL>
SQL>
SQL>
SQL>
SQL> select PRODUCT_NAME,
  2         PRODUCT_PRICE,
  3         QUANTITY_ON_HAND,
  4         to_char(LAST_STOCK_DATE, "MON DD, YYYY HH24:MI") LAST_STOCK_DATE
  5  from   PRODUCT;
PRODUCT_NAME              PRODUCT_PRICE QUANTITY_ON_HAND LAST_STOCK_DATE
------------------------- ------------- ---------------- ------------------
Small Widget                         99                1 JAN 15, 2003 00:00
Medium Widget                        75             1000 JAN 15, 2002 00:00
Product Number                       50              100 JAN 15, 2003 00:00
Round Church Station                 25            10000
SQL>
SQL>
SQL>
SQL> DROP TABLE product;
Table dropped.
SQL>
SQL>
 </source>
    
   


==to_char(order_date, "hh24:mi:ss")==



   
  <!-- start source code -->
   
    <source lang="sql">
 
SQL>
SQL> create table ord(
  2           order_no               integer          primary key
  3          ,cust_no                integer
  4          ,order_date             date not null
  5          ,total_order_price      number(7,2)
  6          ,deliver_date           date
  7          ,deliver_time           varchar2(7)
  8          ,payment_method         varchar2(2)
  9          ,emp_no                 number(3,0)
 10          ,deliver_name           varchar2(35)
 11          ,gift_message           varchar2(100)
 12  );
Table created.
SQL>
SQL>
SQL> insert into ord(order_no,cust_no,order_date,total_order_price,deliver_date,deliver_time,payment_method,emp_no,deliver_name,gift_message)
  2           values(1,1,"14-Feb-2002", 23.00, "14-Feb-2002", "12 noon", "CA",1, null, "Gift for wife");
1 row created.
SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time ,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(2,1,"14-Feb-2003", 510.98, "14-feb-2003", "5 pm", "NY",7, "Rose Ted", "Happy Valentines Day to Mother");
1 row created.
SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(3, 2,"14-Feb-2004", 315.99, "14-feb-2004", "3 pm", "VS",2, "Ani Forest", "Happy Valentines Day to Father");
1 row created.
SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(4, 2,"14-Feb-1999", 191.95, "14-feb-1999", "2 pm", "NJ",2, "O. John", "Happy Valentines Day");
1 row created.
SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message    )
  2           values(5, 6,"4-mar-2002", 101.95, "5-mar-2002", "2:30 pm", "MO"   , 2, "Cora", "Happy Birthday from John");
1 row created.
SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(6, 9,"7-apr-2003", 221.95, "7-apr-2003", "3 pm", "MA", 2, "Sake Keith", "Happy Birthday from Joe" );
1 row created.
SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(7, 9,"20-jun-2004", 315.95, "21-jun-2004", "12 noon", "BC", 2, "Jessica Li", "Happy Birthday from Jessica");
1 row created.
SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values (8, 12, "31-dec-1999", 135.95, "1-jan-2000", "12 noon", "DI",      3, "Larry", "Happy New Year from Lawrence");
1 row created.
SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values (9, 12, "26-dec-2003", 715.95, "2-jan-2004", "12 noon", "SK",7, "Did", "Happy Birthday from Nancy" );
1 row created.
SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(10, 4, sysdate-1, 119.95, sysdate+2, "6:30 pm", "VG",2, "P. Jing", "Happy Valentines Day to Jason");
1 row created.
SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(11, 2, sysdate, 310.00, sysdate+2, "3:30 pm", "DC",2, "C. Late", "Happy Birthday Day to Jack");
1 row created.
SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
  2           values(12, 7, sysdate-3, 121.95, sysdate-2, "1:30 pm", "AC",2, "W. Last", "Happy Birthday Day to You");
1 row created.
SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
  2           values(13, 7, sysdate, 211.95, sysdate-4, "4:30 pm", "CA",2, "J. Bond", "Thanks for hard working");
1 row created.
SQL>
SQL>
SQL> select order_no, order_date from ord;
  ORDER_NO ORDER_DATE
---------- --------------------
         1 14-FEB-2002 00:00:00
         2 14-FEB-2003 00:00:00
         3 14-FEB-2004 00:00:00
         4 14-FEB-1999 00:00:00
         5 04-MAR-2002 00:00:00
         6 07-APR-2003 00:00:00
         7 20-JUN-2004 00:00:00
         8 31-DEC-1999 00:00:00
         9 26-DEC-2003 00:00:00
        10 15-JUN-2008 18:01:18
        11 16-JUN-2008 18:01:18
        12 13-JUN-2008 18:01:18
        13 16-JUN-2008 18:01:18
13 rows selected.
SQL>
SQL> select order_no, to_char(order_date, "hh24:mi:ss")
  2         as "Order Time"
  3    from ord;
  ORDER_NO Order Ti
---------- --------
         1 00:00:00
         2 00:00:00
         3 00:00:00
         4 00:00:00
         5 00:00:00
         6 00:00:00
         7 00:00:00
         8 00:00:00
         9 00:00:00
        10 18:01:18
        11 18:01:18
        12 18:01:18
        13 18:01:18
13 rows selected.
SQL>
SQL> drop table ord;
Table dropped.
SQL> --
 </source>
    
   


==to_char( sysdate, "HH24:MI:SS" )==



   
  <!-- start source code -->
   
    <source lang="sql">
 
SQL>
SQL>
SQL>
SQL> select to_char( sysdate, "HH24:MI:SS" ) "DATE" from dual;
DATE
--------
20:44:28
SQL>
 </source>
    
   


==TO_CHAR(SYSDATE, "MONTH DDSP")==



   
  <!-- start source code -->
   
    <source lang="sql">
 
SQL>
SQL>
SQL> SELECT TO_CHAR(SYSDATE, "MONTH DDSP")
  2  FROM DUAL;
TO_CHAR(SYSDATE,"MONTH
----------------------
JUNE      NINETEEN
SQL>
SQL>
 </source>
    
   


==TO_CHAR(SYSDATE, "MONTH DDSPTH")==



   
  <!-- start source code -->
   
    <source lang="sql">
 
SQL>
SQL> SELECT TO_CHAR(SYSDATE, "MONTH DDSPTH")
  2  FROM DUAL;
TO_CHAR(SYSDATE,"MONTHDD
------------------------
JUNE      NINETEENTH
SQL>
SQL>
 </source>
    
   


==TO_CHAR(SYSDATE, "MONTH DDTH")==



   
  <!-- start source code -->
   
    <source lang="sql">
 
SQL>
SQL> SELECT TO_CHAR(SYSDATE, "MONTH DDTH")
  2  FROM DUAL;
TO_CHAR(SYSDAT
--------------
JUNE      19TH
SQL>
 </source>
    
   


==Use string returned to_char from date type column in where clause==



   
  <!-- start source code -->
   
    <source lang="sql">
 
SQL>
SQL> create table ord(
  2           order_no               integer          primary key
  3          ,cust_no                integer
  4          ,order_date             date not null
  5          ,total_order_price      number(7,2)
  6          ,deliver_date           date
  7          ,deliver_time           varchar2(7)
  8          ,payment_method         varchar2(2)
  9          ,emp_no                 number(3,0)
 10          ,deliver_name           varchar2(35)
 11          ,gift_message           varchar2(100)
 12  );
Table created.
SQL>
SQL>
SQL> insert into ord(order_no,cust_no,order_date,total_order_price,deliver_date,deliver_time,payment_method,emp_no,deliver_name,gift_message)
  2           values(1,1,"14-Feb-2002", 23.00, "14-Feb-2002", "12 noon", "CA",1, null, "Gift for wife");
1 row created.
SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time ,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(2,1,"14-Feb-2003", 510.98, "14-feb-2003", "5 pm", "NY",7, "Rose Ted", "Happy Valentines Day to Mother");
1 row created.
SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(3, 2,"14-Feb-2004", 315.99, "14-feb-2004", "3 pm", "VS",2, "Ani Forest", "Happy Valentines Day to Father");
1 row created.
SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(4, 2,"14-Feb-1999", 191.95, "14-feb-1999", "2 pm", "NJ",2, "O. John", "Happy Valentines Day");
1 row created.
SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message    )
  2           values(5, 6,"4-mar-2002", 101.95, "5-mar-2002", "2:30 pm", "MO"   , 2, "Cora", "Happy Birthday from John");
1 row created.
SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(6, 9,"7-apr-2003", 221.95, "7-apr-2003", "3 pm", "MA", 2, "Sake Keith", "Happy Birthday from Joe" );
1 row created.
SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(7, 9,"20-jun-2004", 315.95, "21-jun-2004", "12 noon", "BC", 2, "Jessica Li", "Happy Birthday from Jessica");
1 row created.
SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values (8, 12, "31-dec-1999", 135.95, "1-jan-2000", "12 noon", "DI",      3, "Larry", "Happy New Year from Lawrence");
1 row created.
SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values (9, 12, "26-dec-2003", 715.95, "2-jan-2004", "12 noon", "SK",7, "Did", "Happy Birthday from Nancy" );
1 row created.
SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(10, 4, sysdate-1, 119.95, sysdate+2, "6:30 pm", "VG",2, "P. Jing", "Happy Valentines Day to Jason");
1 row created.
SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(11, 2, sysdate, 310.00, sysdate+2, "3:30 pm", "DC",2, "C. Late", "Happy Birthday Day to Jack");
1 row created.
SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
  2           values(12, 7, sysdate-3, 121.95, sysdate-2, "1:30 pm", "AC",2, "W. Last", "Happy Birthday Day to You");
1 row created.
SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
  2           values(13, 7, sysdate, 211.95, sysdate-4, "4:30 pm", "CA",2, "J. Bond", "Thanks for hard working");
1 row created.
SQL>
SQL>
SQL> select order_no, order_date
  2    from ord
  3   where trim(to_char(order_date, "Month")) = "February";
  ORDER_NO ORDER_DATE
---------- --------------------
         1 14-FEB-2002 00:00:00
         2 14-FEB-2003 00:00:00
         3 14-FEB-2004 00:00:00
         4 14-FEB-1999 00:00:00
4 rows selected.
SQL>
SQL> drop table ord;
Table dropped.
SQL> --
 </source>
    
   


==Use to_char to format a date type column==



   
  <!-- start source code -->
   
    <source lang="sql">
 
SQL>
SQL>
SQL> CREATE TABLE EMP
  2  (EMPNO NUMBER(4) NOT NULL,
  3   ENAME VARCHAR2(10),
  4   JOB VARCHAR2(9),
  5   MGR NUMBER(4),
  6   HIREDATE DATE,
  7   SAL NUMBER(7, 2),
  8   COMM NUMBER(7, 2),
  9   DEPTNO NUMBER(2)
 10  );
Table created.
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH",  "CLERK",     7902,TO_DATE("17-DEC-1980", "DD-MON-YYYY"),  800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN",  "SALESMAN",  7698,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600,  300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD",   "SALESMAN",  7698,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250,  500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES",  "MANAGER",   7839,TO_DATE("2-APR-1981", "DD-MON-YYYY"),  2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN",  7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE",  "MANAGER",   7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"),  2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK",  "MANAGER",   7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"),  2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT",  "ANALYST",   7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING",   "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN",  7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"),  1500,    0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS",  "CLERK",     7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES",  "CLERK",     7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"),   950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD",   "ANALYST",   7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"),  3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK",     7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL> select * from emp;
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
14 rows selected.
SQL>
SQL> select to_char( hiredate, "Day Mon, YYYY" )
  2  from emp
  3  /
TO_CHAR(HIREDATE,"D
-------------------
Wednesday Dec, 1980
Friday    Feb, 1981
Sunday    Feb, 1981
Thursday  Apr, 1981
Monday    Sep, 1981
Friday    May, 1981
Tuesday   Jun, 1981
Thursday  Dec, 1982
Tuesday   Nov, 1981
Tuesday   Sep, 1981
Wednesday Jan, 1983
Thursday  Dec, 1981
Thursday  Dec, 1981
Saturday  Jan, 1982
14 rows selected.
SQL>
SQL> drop table emp;
Table dropped.
SQL> --
 </source>