Oracle PL/SQL/Data Type/Date

Материал из SQL эксперт
Версия от 09:58, 26 мая 2010; Admin (обсуждение | вклад) (1 версия)
(разн.) ← Предыдущая | Текущая версия (разн.) | Следующая → (разн.)
Перейти к: навигация, поиск

Are two dates equal

     
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 ("Wodget",       75, 1000, "15-JAN-02");
1 row created.
SQL> INSERT INTO product VALUES ("Product 1",    50, 100,  "15-JAN-03");
1 row created.
SQL> INSERT INTO product VALUES ("Product 2",    25, 10000, null);
1 row created.
SQL>
SQL> SELECT * FROM product WHERE  last_stock_date = "15-JAN-2003";
PRODUCT_NAME              PRODUCT_PRICE QUANTITY_ON_HAND LAST_STOC
------------------------- ------------- ---------------- ---------
Small Widget                         99                1 15-JAN-03
Product 1                            50              100 15-JAN-03
2 rows selected.
SQL>
SQL>
SQL> DROP TABLE product;
Table dropped.
SQL>
SQL>
SQL>
SQL>



Assign date value as string to date type variable

    
SQL> Declare
  2           myDate   date;
  3  Begin
  4           myDate := "29-SEP-2005";
  5           dbms_output.putline(myDate); --show date
  6  End;
  7  /
SQL>



Assign system date to date type variable

    

SQL>
SQL> declare
  2      l_today date    := sysdate;
  3  begin
  4      Null;
  5  exception
  6      when others then
  7          raise_application_error (-20100, "error#" || sqlcode || " desc: " || sqlerrm);
  8  end;
  9  /
PL/SQL procedure successfully completed.
SQL>
SQL>



check date value range

     
SQL>
SQL> CREATE OR REPLACE FUNCTION date_range
  2     (p_low_end_date  DATE,
  3      p_high_end_date DATE,
  4      p_to_check_date DATE)
  5      RETURN BOOLEAN IS
  6
  7      returnBoolean BOOLEAN := FALSE;
  8      smallDate   DATE := TRUNC(p_low_end_date); -- Time 00:00:00
  9      largeDate  DATE := TRUNC(p_high_end_date + 1) - .000011574;
 10  BEGIN
 11     IF p_to_check_date >  smallDate  AND
 12        p_to_check_date <= largeDate THEN
 13        returnBoolean := TRUE;
 14     END IF;
 15
 16     DBMS_OUTPUT.PUT_LINE("Low Date     : " || TO_CHAR(smallDate, "MM/DD/YYYY:HH24:MI:SS"));
 17     DBMS_OUTPUT.PUT_LINE("Date To Check: " || TO_CHAR(p_to_check_date, "MM/DD/YYYY:HH24:MI:SS"));
 18     DBMS_OUTPUT.PUT_LINE("High Date    : " || TO_CHAR(largeDate,    "MM/DD/YYYY:HH24:MI:SS"));
 19     RETURN returnBoolean;
 20  END date_range;
 21  /
Function created.
SQL>
SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL> DECLARE
  2     checkBoolean BOOLEAN;
  3  BEGIN
  4     checkBoolean := date_range(SYSDATE, SYSDATE, SYSDATE);
  5     IF checkBoolean THEN
  6        DBMS_OUTPUT.PUT_LINE("Date in Range: TRUE");
  7     ELSE
  8        DBMS_OUTPUT.PUT_LINE("Date in Range: FALSE");
  9    END IF;
 10  END;
 11  /
Low Date     : 06/19/2008:00:00:00
Date To Check: 06/19/2008:18:55:03
High Date    : 06/19/2008:23:59:59
Date in Range: TRUE
PL/SQL procedure successfully completed.
SQL>



Compare date value after to_char() and trim()

    
SQL> create table gift(
  2           gift_id                integer         primary key
  3          ,emp_id                integer
  4          ,register_date              date not null
  5          ,total_price        number(7,2)
  6          ,deliver_date           date
  7          ,deliver_time           varchar2(7)
  8          ,payment        varchar2(2)
  9          ,emp_no                 number(3,0)
 10          ,deliver_name           varchar2(35)
 11          ,message        varchar2(100)
 12  );
Table created.
SQL>
SQL> insert into gift(gift_id,emp_id,register_date,total_price,deliver_date,deliver_time,payment,emp_no,deliver_name,message)values
  2                 (1,1,"14-Feb-1999", 123.12, "14-Feb-1999", "12 noon", "CA",1, null, "Happy Birthday to you");
1 row created.
SQL> insert into gift(gift_id  ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time ,payment ,emp_no,deliver_name ,message)values
  2                 (2,1,"14-Feb-1999", 50.98, "14-feb-1999", "1 pm", "CA",7, "name1", "Happy Birthday");
1 row created.
SQL> insert into gift(gift_id  ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
  2                 (3, 2,"14-Feb-1999", 35.99, "14-feb-1999", "1 pm", "VS",2, "Tom", "Happy Birthday");
1 row created.
SQL> insert into gift(gift_id  ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
  2                 (4, 2,"14-Feb-1999", 19.95, "14-feb-1999", "5 pm", "CA",2, "Mary", "Happy Birthday");
1 row created.
SQL> insert into gift(gift_id  ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message)values
  2                 (5, 6,"4-mar-1999", 10.95, "5-mar-1999", "4:30 pm", "VS", 2, "Jack", "Happy Birthday");
1 row created.
SQL>
SQL>
SQL>
SQL> select gift_id, register_date
  2    from gift
  3   where trim(to_char(register_date, "Month")) = "February";
   GIFT_ID REGISTER_
---------- ---------
         1 14-FEB-99
         2 14-FEB-99
         3 14-FEB-99
         4 14-FEB-99
4 rows selected.
SQL>
SQL>
SQL> drop table gift;
Table dropped.



Compare date value with sysdate or null value

    
SQL>
SQL> CREATE TABLE emp (
  2    emp_id               NUMBER,
  3    ename             VARCHAR2(40),
  4    hire_date        DATE DEFAULT sysdate,
  5    end_date DATE,
  6    rate     NUMBER(5,2),
  7    CONSTRAINT emp_pk    PRIMARY KEY (emp_id)
  8  );
Table created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (101, "Mary", to_date("15-Nov-1961","dd-mon-yyyy"),null,169);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (102, "Tom", to_date("16-Sep-1964","dd-mon-yyyy"),to_date("5-May-2004","dd-mon-yyyy"),135);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (104, "Peter", to_date("29-Dec-1987","dd-mon-yyyy"),to_date("1-Apr-2004","dd-mon-yyyy"),99);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (105, "Mike", to_date("15-Jun-2004","dd-mon-yyyy"),null,121);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (107, "Less", to_date("2-Jan-2004","dd-mon-yyyy"),null,45);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (108, "Park", to_date("1-Mar-1994","dd-mon-yyyy"),to_date("15-Nov-2004","dd-mon-yyyy"),220);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (110, "Ink", to_date("4-Apr-2004","dd-mon-yyyy"),to_date("30-Sep-2004","dd-mon-yyyy"),84);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (111, "Tike", to_date("23-Aug-1976","dd-mon-yyyy"),null,100);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (112, "Inn", to_date("15-Nov-1961","dd-mon-yyyy"),to_date("4-Apr-2004","dd-mon-yyyy"),70);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (113, "Kate", to_date("3-Mar-2004","dd-mon-yyyy"),to_date("31-Oct-2004","dd-mon-yyyy"),300);
1 row created.
SQL>
SQL>
SQL> SET ECHO ON
SQL>
SQL> SELECT ename
  2  FROM emp
  3  WHERE end_date <> SYSDATE;
Tom
Peter
Park
Ink
Inn
Kate
6 rows selected.
SQL>
SQL> SELECT ename
  2  FROM emp
  3  WHERE end_date = SYSDATE;
no rows selected
SQL>
SQL> SELECT ename
  2  FROM emp
  3  WHERE end_date = NULL;
no rows selected
SQL>
SQL>
SQL> drop table emp;
Table dropped.



Convert the character string back to date format

    
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2     v_sysdate DATE := SYSDATE;
  3     v_date DATE;
  4     v_char VARCHAR2(20);
  5  BEGIN
  6
  7     
  8     DBMS_OUTPUT.PUT_LINE("Today""s Date: "||v_sysdate);
  9
 10     
 11     v_date := TO_DATE(v_char, "DD:MM:YYYY HH24:MI:SS");
 12     DBMS_OUTPUT.PUT_LINE("Convert back to DATE format: "||v_date);
 13
 14  END;
 15  /
Today"s Date: 26-OCT-09
Convert back to DATE format:
PL/SQL procedure successfully completed.
SQL>
SQL>



Date data types

    
SQL>
SQL> set serverout on;
SQL>
SQL> -- Date datatypes
SQL>
SQL>
SQL> DECLARE
  2     myDate Date;
  3
  4  BEGIN
  5     myDate := sysdate;
  6
  7     DBMS_OUTPUT.PUT_LINE(myDate);
  8
  9  END;
 10  /
10-SEP-06
PL/SQL procedure successfully completed.
SQL>



Date math

    
SQL>
SQL>
SQL> create table emp
  2  ( empno      NUMBER(4)    constraint E_PK primary key
  3  , ename      VARCHAR2(8)
  4  , init       VARCHAR2(5)
  5  , job        VARCHAR2(8)
  6  , mgr        NUMBER(4)
  7  , bdate      DATE
  8  , sal       NUMBER(6,2)
  9  , comm       NUMBER(6,2)
 10  , deptno     NUMBER(2)    default 10
 11  ) ;
Table created.
SQL> insert into emp values(1,"Tom","N",   "Coder", 13,date "1965-12-17",  800 , NULL,  20);
1 row created.
SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20",  1600, 300,   30);
1 row created.
SQL> insert into emp values(3,"Wil","TF" ,  "Tester",6,date "1962-02-22",  1250, 500,   30);
1 row created.
SQL> insert into emp values(4,"Jane","JM",  "Designer", 9,date "1967-04-02",  2975, NULL,  20);
1 row created.
SQL> insert into emp values(5,"Mary","P",  "Tester",6,date "1956-09-28",  1250, 1400,  30);
1 row created.
SQL> insert into emp values(6,"Black","R",   "Designer", 9,date "1963-11-01",  2850, NULL,  30);
1 row created.
SQL> insert into emp values(7,"Chris","AB",  "Designer", 9,date "1965-06-09",  2450, NULL,  10);
1 row created.
SQL> insert into emp values(8,"Smart","SCJ", "Coder", 4,date "1959-11-26",  3000, NULL,  20);
1 row created.
SQL> insert into emp values(9,"Peter","CC",   "Designer",NULL,date "1952-11-17",  5000, NULL,  10);
1 row created.
SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28",  1500, 0,     30);
1 row created.
SQL> insert into emp values(11,"Ana","AA",  "Coder", 8,date "1966-12-30",  1100, NULL,  20);
1 row created.
SQL> insert into emp values(12,"Jane","R",   "Manager",   6,date "1969-12-03",  800 , NULL,  30);
1 row created.
SQL> insert into emp values(13,"Fake","MG",   "Coder", 4,date "1959-02-13",  3000, NULL,  20);
1 row created.
SQL> insert into emp values(14,"Mike","TJA","Manager",   7,date "1962-01-23",  1300, NULL,  10);
1 row created.
SQL>
SQL>
SQL> select ename, (sysdate-bdate)/365
  2  from   emp
  3  where  empno = 9;
ENAME    (SYSDATE-BDATE)/365
-------- -------------------
Peter             56.9792274
SQL>
SQL>
SQL> drop table emp;
Table dropped.



Date/Time literals

    
SQL>
SQL>
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
  2     v_date DATE := DATE "2004-06-05";
  3  BEGIN
  4     DBMS_OUTPUT.PUT_LINE(v_date);
  5  END;
  6  /
05-JUN-04
PL/SQL procedure successfully completed.
SQL>
SQL>



Date type column

    
SQL> create table myTable (d date);
Table created.
SQL>
SQL> insert into myTable values (sysdate);
1 row created.
SQL>
SQL> select * from myTable;

D
---------
26-OCT-09
1 row selected.
SQL>
SQL> drop table myTable;
Table dropped.



Date value calculation in to_char function

    
SQL>
SQL>
SQL> select to_char(date "1954-08-11" + 10000,"Day")
  2         as "On a:"
  3  from   dual;
Enter...
Zondag
1 row selected.
SQL>



Date variable

    
SQL>
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2     v_sysdate DATE := SYSDATE;
  3     v_date DATE;
  4     v_char VARCHAR2(20);
  5  BEGIN
  6     
  7     DBMS_OUTPUT.PUT_LINE("Today""s Date: "||v_sysdate);
  8
  9
 10  END;
 11  /
Today"s Date: 26-OCT-09
PL/SQL procedure successfully completed.



Declare date type variable and set the value at the same time

    
SQL>
SQL>
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
  2     v_date DATE := DATE "2004-06-05";
  3  BEGIN
  4     DBMS_OUTPUT.PUT_LINE(v_date);
  5  END;
  6  /
05-JUN-04
PL/SQL procedure successfully completed.
SQL>
SQL>



Define and use Date data type

    

SQL> set echo on;
SQL> DECLARE
  2     x         NUMBER;
  3     v_message VARCHAR2(20);
  4     v_today   DATE;
  5  BEGIN
  6     x := 10;
  7     DBMS_OUTPUT.PUT_LINE(x);
  8
  9     x := 2E5;
 10     DBMS_OUTPUT.PUT_LINE(x);
 11
 12     v_message :="www.sqle.ru";
 13     DBMS_OUTPUT.PUT_LINE("v_message");
 14
 15     v_today :="01-JAN-00"; -- default date format DD-MON-YY
 16     DBMS_OUTPUT.PUT_LINE(v_today);
 17
 18     v_today := SYSDATE;     -- SQL function
 19     DBMS_OUTPUT.PUT_LINE(v_today);
 20  END;
 21  /
10
200000
v_message
01-JAN-00
09-SEP-06
PL/SQL procedure successfully completed.
SQL>



Differences Between Dates

    
SQL>
SQL> CREATE TABLE dept (
  2     deptID INT NOT NULL PRIMARY KEY,
  3     StudentID    INT NOT NULL,
  4     ClassID      INT NOT NULL,
  5     EnrolledOn   DATE,
  6     Grade        INT);
Table created.
SQL>
SQL> INSERT INTO dept (deptID,StudentID,ClassID,EnrolledOn,Grade) VALUES (1,1,1,DATE "2002-09-23",62);
1 row created.
SQL> INSERT INTO dept (deptID,StudentID,ClassID,EnrolledOn,Grade) VALUES (2,1,2,DATE "2002-09-30",70);
1 row created.
SQL> INSERT INTO dept (deptID,StudentID,ClassID,EnrolledOn,Grade) VALUES (3,2,3,DATE "2003-09-23",51);
1 row created.
SQL> INSERT INTO dept (deptID,StudentID,ClassID,EnrolledOn,Grade) VALUES (4,2,5,DATE "2003-09-23",41);
1 row created.
SQL> INSERT INTO dept (deptID,StudentID,ClassID,EnrolledOn,Grade) VALUES (5,2,6,DATE "2003-09-23",68);
1 row created.
SQL> INSERT INTO dept (deptID,StudentID,ClassID,EnrolledOn,Grade) VALUES (6,3,4,DATE "2002-09-30",78);
1 row created.
SQL> INSERT INTO dept (deptID,StudentID,ClassID,EnrolledOn,Grade) VALUES (7,3,7,DATE "2002-09-30",80);
1 row created.
SQL> INSERT INTO dept (deptID,StudentID,ClassID,EnrolledOn,Grade) VALUES (8,4,8,DATE "2002-09-20",70);
1 row created.
SQL> INSERT INTO dept (deptID,StudentID,ClassID,EnrolledOn,Grade) VALUES (9,4,5,DATE "2002-09-20",60);
1 row created.
SQL> INSERT INTO dept (deptID,StudentID,ClassID,EnrolledOn,Grade) VALUES (10,5,1,DATE "2002-09-23",33);
1 row created.
SQL>
SQL>
SQL> SELECT StudentID,
  2         FLOOR(MAX(CURRENT_DATE - EnrolledOn)) AS DaysEnrolled
  3  FROM dept
  4  GROUP BY StudentID
  5  ORDER BY DaysEnrolled DESC;
 STUDENTID DAYSENROLLED
---------- ------------
         4         2593
         1         2590
         5         2590
         3         2583
         2         2225
5 rows selected.
SQL>
SQL>
SQL> drop table dept;
Table dropped.



Extract year, month, day from a date

    
SQL> create table emp
  2  ( empno      NUMBER(4)    constraint E_PK primary key
  3  , ename      VARCHAR2(8)
  4  , init       VARCHAR2(5)
  5  , job        VARCHAR2(8)
  6  , mgr        NUMBER(4)
  7  , bdate      DATE
  8  , sal       NUMBER(6,2)
  9  , comm       NUMBER(6,2)
 10  , deptno     NUMBER(2)    default 10
 11  ) ;
Table created.
SQL> insert into emp values(1,"Tom","N",   "Coder", 13,date "1965-12-17",  800 , NULL,  20);
1 row created.
SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20",  1600, 300,   30);
1 row created.
SQL> insert into emp values(3,"Wil","TF" ,  "Tester",6,date "1962-02-22",  1250, 500,   30);
1 row created.
SQL> insert into emp values(4,"Jane","JM",  "Designer", 9,date "1967-04-02",  2975, NULL,  20);
1 row created.
SQL> insert into emp values(5,"Mary","P",  "Tester",6,date "1956-09-28",  1250, 1400,  30);
1 row created.
SQL> insert into emp values(6,"Black","R",   "Designer", 9,date "1963-11-01",  2850, NULL,  30);
1 row created.
SQL> insert into emp values(7,"Chris","AB",  "Designer", 9,date "1965-06-09",  2450, NULL,  10);
1 row created.
SQL> insert into emp values(8,"Smart","SCJ", "Coder", 4,date "1959-11-26",  3000, NULL,  20);
1 row created.
SQL> insert into emp values(9,"Peter","CC",   "Designer",NULL,date "1952-11-17",  5000, NULL,  10);
1 row created.
SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28",  1500, 0,     30);
1 row created.
SQL> insert into emp values(11,"Ana","AA",  "Coder", 8,date "1966-12-30",  1100, NULL,  20);
1 row created.
SQL> insert into emp values(12,"Jane","R",   "Manager",   6,date "1969-12-03",  800 , NULL,  30);
1 row created.
SQL> insert into emp values(13,"Fake","MG",   "Coder", 4,date "1959-02-13",  3000, NULL,  20);
1 row created.
SQL> insert into emp values(14,"Mike","TJA","Manager",   7,date "1962-01-23",  1300, NULL,  10);
1 row created.
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
SQL>
SQL>
SQL> drop table emp;
Table dropped.



Initialize value with date functions

     
SQL>
SQL> DECLARE
  2     lv_three_month_forecast_date DATE         := ADD_MONTHS(SYSDATE,3);
  3     lv_current_user_txt          VARCHAR2(40) := TO_CHAR(UID) ||
  4                                                  ": " || USER;
  5     lv_date_as_number_num        PLS_INTEGER  := TO_NUMBER(TO_CHAR(
  6                                                  SYSDATE, "YYYYMMDD"));
  7  BEGIN
  8     DBMS_OUTPUT.PUT_LINE(lv_three_month_forecast_date);
  9     DBMS_OUTPUT.PUT_LINE(lv_current_user_txt);
 10     DBMS_OUTPUT.PUT_LINE(lv_date_as_number_num);
 11  END;
 12  /
19-SEP-08
36: sqle
20080619
PL/SQL procedure successfully completed.
SQL>



Insert statement converts text value to date type value

     
SQL>
SQL>
SQL> CREATE TABLE purchase (
  2       product_name  VARCHAR2(25),
  3       salesperson   VARCHAR2(3),
  4       purchase_date DATE,
  5       quantity      NUMBER(4,2));
Table created.
SQL>
SQL> INSERT INTO purchase VALUES ("Small Widget", "CA", "14-JUL-03", 1);
1 row created.
SQL> INSERT INTO purchase VALUES ("Medium Widget", "BB", "14-JUL-03", 75);
1 row created.
SQL> INSERT INTO purchase VALUES ("Product Number", "GA", "14-JUL-03", 2);
1 row created.
SQL> INSERT INTO purchase VALUES ("Small Widget", "GA", "15-JUL-03", 8);
1 row created.
SQL> INSERT INTO purchase VALUES ("Medium Widget", "LB", "15-JUL-03", 20);
1 row created.
SQL> INSERT INTO purchase VALUES ("Round Station", "CA", "16-JUL-03", 5);
1 row created.
SQL>
SQL> DROP TABLE purchase;
Table dropped.
SQL>
SQL>
SQL>



Insert sysdate value to date type column

    
SQL> create table myTable (d date);
Table created.
SQL>
SQL> insert into myTable values (sysdate);
1 row created.
SQL>
SQL> select * from myTable;

D
---------
26-OCT-09
1 row selected.
SQL>
SQL> drop table myTable;
Table dropped.



New ANSI DATE literal.

    
SQL>
SQL> CREATE TABLE T (
  2      c1  DATE,
  3      c2  TIMESTAMP,
  4      c3  TIMESTAMP WITH TIME ZONE,
  5      c4  TIMESTAMP WITH LOCAL TIME ZONE
  6  );
Table created.
SQL>
SQL> DECLARE
  2
  3      v1  DATE;
  4      v2  TIMESTAMP;                
  5      v3  TIMESTAMP WITH TIME ZONE; 
  6      v4  TIMESTAMP WITH LOCAL TIME ZONE;  
  7
  8  BEGIN
  9      v1 := DATE "2002-11-01";
 10      dbms_output.put_line(v1);
 11
 12  END;
 13  /
01-NOV-02
PL/SQL procedure successfully completed.
SQL>
SQL> select to_Char(c1, "yyyy-mm-dd hh:mi:ss"), c2,c3,c4 from t;
no rows selected
SQL> DROP TABLE T;
Table dropped.



Set date value to SYSDATE

     
SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
  2                    ENAME VARCHAR2(10),
  3                    JOB VARCHAR2(9),
  4                    MGR NUMBER(4),
  5                    HIREDATE DATE,
  6                    SAL NUMBER(7, 2),
  7                    COMM NUMBER(7, 2),
  8                    DEPTNO NUMBER(2));
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 hiredate from emp;
HIREDATE
---------
17-DEC-80
20-FEB-81
22-FEB-81
02-APR-81
28-SEP-81
01-MAY-81
09-JUN-81
09-DEC-82

                                                                                                                                      Page           1
HIREDATE
---------
17-NOV-81
08-SEP-81
12-JAN-83
03-DEC-81
03-DEC-81
23-JAN-82


                                                                                                                                      Page           2
14 rows selected.
SQL>
SQL> UPDATE emp
  2  SET    hiredate = SYSDATE
  3  WHERE  empno< 7500;
2 rows updated.
SQL>
SQL> select hiredate from emp;
HIREDATE
---------
19-JUN-08
19-JUN-08
22-FEB-81
02-APR-81
28-SEP-81
01-MAY-81
09-JUN-81
09-DEC-82

                                                                                                                                      Page           1
HIREDATE
---------
17-NOV-81
08-SEP-81
12-JAN-83
03-DEC-81
03-DEC-81
23-JAN-82


                                                                                                                                      Page           2
14 rows selected.
SQL>
SQL> drop table emp;
Table dropped.
SQL>
SQL>



Store hour:minute information to date type variable

    
SQL> declare
  2      myDate    date;
  3  Begin
  4      myDate := to_date("09-JUN-91:13:01 ", "DD-MON-YY:HH24:MI");-- Sets variable to June 9, 1991, 1:01p.m.
  5  End;
  6  /
PL/SQL procedure successfully completed.
SQL>



Use comparison operator with date type value

     
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 ("Wodget",       75, 1000, "15-JAN-02");
1 row created.
SQL> INSERT INTO product VALUES ("Product 1",    50, 100,  "15-JAN-03");
1 row created.
SQL> INSERT INTO product VALUES ("Product 2",    25, 10000, null);
1 row created.
SQL>
SQL>
SQL> SELECT * FROM product WHERE  last_stock_date > "31-DEC-02";
PRODUCT_NAME              PRODUCT_PRICE QUANTITY_ON_HAND LAST_STOC
------------------------- ------------- ---------------- ---------
Small Widget                         99                1 15-JAN-03
Product 1                            50              100 15-JAN-03
2 rows selected.
SQL>
SQL>
SQL> DROP TABLE product;
Table dropped.
SQL>
SQL>
SQL>
SQL>



Use DATE to mark a string as date value

     
SQL>
SQL> create table MyTable (
  2        event_name    varchar2(100),
  3        event_date    date);
Table created.
SQL>
SQL> insert into MyTable ( event_name, event_date ) values ( "Oracle", TO_DATE( "2-DEC-2001", "DD-MON-YYYY" ) );
1 row created.
SQL>
SQL> insert into MyTable ( event_name, event_date ) values ( "Sample code", SYSDATE );
1 row created.
SQL>
SQL> column event_name format a40
SQL>
SQL> select * from MyTable;
EVENT_NAME                               EVENT_DATE
---------------------------------------- ------------------
Oracle                                   02-DEC-01
Sample code                              10-JUN-08
SQL>
SQL> insert into MyTable (event_name, event_date) values ("World", TO_DATE( "2-DEC-2001", "DD-MON-YYYY" ) );
1 row created.
SQL>
SQL> insert into MyTable ( event_name, event_date ) values ( "code", SYSDATE );
1 row created.
SQL>
SQL> show parameters nls_date_format
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_date_format                      string
SQL>
SQL> insert into MyTable ( event_name, event_date ) values ( "9i", DATE "2001-10-11" );
1 row created.
SQL>
SQL> select * from MyTable;
EVENT_NAME                               EVENT_DATE
---------------------------------------- ------------------
Oracle                                   02-DEC-01
Sample code                              10-JUN-08
World                                    02-DEC-01
code                                     10-JUN-08
9i                                       11-OCT-01
SQL>
SQL> drop table MyTable;
Table dropped.
SQL>



Use DATE type column

     
SQL>
SQL> create table MyTable (
  2  event_name    varchar2(100),
  3  event_date    date);
Table created.
SQL>
SQL> insert into MyTable ( event_name, event_date ) values ( "Oracle", TO_DATE( "2-DEC-2001", "DD-MON-YYYY" ) );
1 row created.
SQL>
SQL> insert into MyTable ( event_name, event_date ) values ( "Sample code", SYSDATE );
1 row created.
SQL>
SQL> column event_name format a40
SQL>
SQL> select * from MyTable;
EVENT_NAME                               EVENT_DATE
---------------------------------------- ------------------
Oracle                                   02-DEC-01
Sample code                              10-JUN-08
SQL> insert into MyTable (event_name, event_date) values ("World", TO_DATE( "2-DEC-2001", "DD-MON-YYYY" ) );
1 row created.
SQL> insert into MyTable ( event_name, event_date ) values ( "code", SYSDATE );
1 row created.
SQL>
SQL> show parameters nls_date_format
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_date_format                      string
SQL>
SQL> insert into MyTable ( event_name, event_date ) values ( "9i", DATE "2001-10-11" );
1 row created.
SQL>
SQL> select * from MyTable;
EVENT_NAME                               EVENT_DATE
---------------------------------------- ------------------
Oracle                                   02-DEC-01
Sample code                              10-JUN-08
World                                    02-DEC-01
code                                     10-JUN-08
9i                                       11-OCT-01
SQL>
SQL> drop table MyTable;
Table dropped.
SQL>



Use if statement to compare two date type variable

    
SQL> -- create demo table
SQL> create table emp(
  2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
  3    fname         VARCHAR2(10 BYTE),
  4    lname          VARCHAR2(10 BYTE),
  5    Start_Date         DATE,
  6    End_Date           DATE,
  7    Salary             Number(8,2),
  8    City               VARCHAR2(10 BYTE),
  9    Description        VARCHAR2(15 BYTE)
 10  )
 11  /
Table created.
SQL>
SQL>
SQL>
SQL> -- prepare data
SQL> insert into emp(ID,  fname, lname, Start_Date,                     End_Date
,                       Salary,  City,       Description)
  2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMM
DD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
  3  /
1 row created.
SQL> insert into emp(ID,  fname, lname, Start_Date,                     End_Date
,                       Salary,  City,       Description)
  2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMM
DD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
  3  /
1 row created.
SQL>
SQL>
SQL> declare
  2    dateValue date;
  3    s Number(8,2);
  4    begin
  5         select start_date into dateValue from emp where rownum = 1;
  6         select salary into s from emp where rownum = 1;
  7
  8          IF dateValue > "11-APR-63" then
  9              s :=  s * 1.15;
 10          END IF;
 11
 12
 13       dbms_output.put_line(s);
 14  end;
 15  /
1419.74
PL/SQL procedure successfully completed.
SQL>
SQL> drop table emp;
Table dropped.
SQL>



Use SYSDATE in insert statement

     
SQL>
SQL> create table MyTable (
  2        event_name    varchar2(100),
  3        event_date    date);
Table created.
SQL>
SQL> insert into MyTable ( event_name, event_date ) values ( "Oracle", TO_DATE( "2-DEC-2001", "DD-MON-YYYY" ) );
1 row created.
SQL>
SQL> insert into MyTable ( event_name, event_date ) values ( "Sample code", SYSDATE );
1 row created.
SQL>
SQL> column event_name format a40
SQL>
SQL> select * from MyTable;
EVENT_NAME                               EVENT_DATE
---------------------------------------- ------------------
Oracle                                   02-DEC-01
Sample code                              10-JUN-08
SQL>
SQL> insert into MyTable (event_name, event_date) values ("World", TO_DATE( "2-DEC-2001", "DD-MON-YYYY" ) );
1 row created.
SQL>
SQL> insert into MyTable ( event_name, event_date ) values ( "code", SYSDATE );
1 row created.
SQL>
SQL> show parameters nls_date_format
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_date_format                      string
SQL>
SQL> insert into MyTable ( event_name, event_date ) values ( "9i", DATE "2001-10-11" );
1 row created.
SQL>
SQL> select * from MyTable;
EVENT_NAME                               EVENT_DATE
---------------------------------------- ------------------
Oracle                                   02-DEC-01
Sample code                              10-JUN-08
World                                    02-DEC-01
code                                     10-JUN-08
9i                                       11-OCT-01
SQL>
SQL> drop table MyTable;
Table dropped.
SQL>



use to_char more than once to create long date format

    
SQL>
SQL>
SQL> create table gift(
  2           gift_id                integer         primary key
  3          ,emp_id                integer
  4          ,register_date              date not null
  5          ,total_price        number(7,2)
  6          ,deliver_date           date
  7          ,deliver_time           varchar2(7)
  8          ,payment        varchar2(2)
  9          ,emp_no                 number(3,0)
 10          ,deliver_name           varchar2(35)
 11          ,message        varchar2(100)
 12  );
Table created.
SQL>
SQL> insert into gift(gift_id,emp_id,register_date,total_price,deliver_date,deliver_time,payment,emp_no,deliver_name,message)values
  2                 (1,1,"14-Feb-1999", 123.12, "14-Feb-1999", "12 noon", "CA",1, null, "Happy Birthday to you");
1 row created.
SQL> insert into gift(gift_id  ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time ,payment ,emp_no,deliver_name ,message)values
  2                 (2,1,"14-Feb-1999", 50.98, "14-feb-1999", "1 pm", "CA",7, "name1", "Happy Birthday");
1 row created.
SQL> insert into gift(gift_id  ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
  2                 (3, 2,"14-Feb-1999", 35.99, "14-feb-1999", "1 pm", "VS",2, "Tom", "Happy Birthday");
1 row created.
SQL> insert into gift(gift_id  ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
  2                 (4, 2,"14-Feb-1999", 19.95, "14-feb-1999", "5 pm", "CA",2, "Mary", "Happy Birthday");
1 row created.
SQL> insert into gift(gift_id  ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message)values
  2                 (5, 6,"4-mar-1999", 10.95, "5-mar-1999", "4:30 pm", "VS", 2, "Jack", "Happy Birthday");
1 row created.
SQL>
SQL>
SQL> select gift_id, "Order placed on the " ||
  2         to_char(register_date, "fmddth") || " day of "||
  3         to_char(register_date, "fmMonth") || ", " ||
  4         to_char(register_date, "yyyy") as "Order date"
  5    from gift;
   GIFT_ID Order date
---------- -----------------------------------------------
         1 Order placed on the 14th day of February, 1999
         2 Order placed on the 14th day of February, 1999
         3 Order placed on the 14th day of February, 1999
         4 Order placed on the 14th day of February, 1999
         5 Order placed on the 4th day of March, 1999
5 rows selected.
SQL>
SQL>
SQL>
SQL> drop table gift;
Table dropped.



Use to_char to format date

    
SQL>
SQL>
SQL>
SQL> create table emp(
  2           emp_no                 integer         primary key,
  3           lastname               varchar2(20)    not null,
  4           firstname              varchar2(15)    not null,
  5           midinit                varchar2(1),
  6           street                 varchar2(30),
  7           city                   varchar2(20),
  8           state                  varchar2(2),
  9           zip                    varchar2(5),
 10           shortZipCode                   varchar2(4),
 11           area_code              varchar2(3),
 12           phone                  varchar2(8),
 13           salary                 number(5,2),
 14           birthdate              date,
 15           startDate              date,
 16           title                  varchar2(20),
 17           dept_no                integer         ,
 18           mgr                    integer,
 19           region                 number,
 20           division               number,
 21           total_sales            number
 22  );
Table created.
SQL> -- emp Table Inserts:
SQL> insert into emp(emp_no, lastname, firstname, midinit, street, city, state, zip,shortZipCode, area_code, phone, birthdate, title)values
  2                      (1,"Z","Joy","R","1 Ave","New York","NY","12122","2333","212","200-1111","12-nov-1976","President");
1 row created.
SQL> insert into emp(emp_no, lastname, firstname, midinit, street, city, state, zip,shortZipCode, area_code, phone, salary, birthdate, startDate,title, dept_no, mgr, region, division, total_sales)valu
es
  2                      (2,"X","Lucy","J","1 Street","New York","NY","43552","6633","212","234-4444",7.75,"21-mar-1976","1-feb-1994","Sales Manager",2,1,100,10,40000);
1 row created.
SQL> insert into emp(emp_no, lastname, firstname, midinit, street, city, state, zip,shortZipCode, area_code, phone, salary, birthdate, startDate,title, dept_no, mgr, region, division, total_sales)valu
es
  2                      (3,"Y","Jordan","E","1 Drive","New York","NY","76822","8763","212","222-2222",7.75,"14-feb-1963","15-mar-1995","Sales Clerk",2,2,100,10,10000);
1 row created.
SQL>
SQL> SELECT to_char(startDate, "mm/dd/yyyy" )  from emp;
TO_CHAR(ST
----------
02/01/1994
03/15/1995
SQL>
SQL> drop table emp;
Table dropped.
Источник — «http://sqle.ru/index.php?title=Oracle_PL/SQL/Data_Type/Date&oldid=1576»