Oracle PL/SQL/Data Type/Date
Содержание
- 1 Are two dates equal
- 2 Assign date value as string to date type variable
- 3 Assign system date to date type variable
- 4 check date value range
- 5 Compare date value after to_char() and trim()
- 6 Compare date value with sysdate or null value
- 7 Convert the character string back to date format
- 8 Date data types
- 9 Date math
- 10 Date/Time literals
- 11 Date type column
- 12 Date value calculation in to_char function
- 13 Date variable
- 14 Declare date type variable and set the value at the same time
- 15 Define and use Date data type
- 16 Differences Between Dates
- 17 Extract year, month, day from a date
- 18 Initialize value with date functions
- 19 Insert statement converts text value to date type value
- 20 Insert sysdate value to date type column
- 21 New ANSI DATE literal.
- 22 Set date value to SYSDATE
- 23 Store hour:minute information to date type variable
- 24 Use comparison operator with date type value
- 25 Use DATE to mark a string as date value
- 26 Use DATE type column
- 27 Use if statement to compare two date type variable
- 28 Use SYSDATE in insert statement
- 29 use to_char more than once to create long date format
- 30 Use to_char to format date
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.