Oracle PL/SQL Tutorial/SQL Data Types/Date
Содержание
- 1 Add condition constraint to date type column
- 2 BETWEEN for date type value
- 3 Call SYSDATE in insert statement
- 4 Cast string to date in where clause
- 5 Check Date format before inserting
- 6 Combining TO_CHAR() and TO_DATE() Calls
- 7 Compare date type value in where clause
- 8 Converting a DATE to Another Language
- 9 Date calculation
- 10 Default time field
- 11 Greater than a date
- 12 Process and store a specific date and time.
- 13 Query for NULL Date type data
- 14 Setting the Default Date Format
- 15 Storing and Retrieving Dates
- 16 TO_CHAR(start_date, "MM-DD-YYYY HH24:MI")
- 17 TRUNC(Start_date)
- 18 Use the DATE keyword to supply a date literal to the database.
Add condition constraint to date type column
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE product_order (
2 product_name VARCHAR2(25), 3 salesperson VARCHAR2(3), 4 order_date DATE, 5 quantity NUMBER(4,2) 6 );
Table created. SQL> SQL> SQL> INSERT INTO product_order VALUES ("Product 1", "CA", "14-JUL-03", 1); 1 row created. SQL> INSERT INTO product_order VALUES ("Product 2", "BB", "14-JUL-03", 75); 1 row created. SQL> INSERT INTO product_order VALUES ("Product 3", "GA", "14-JUL-03", 2); 1 row created. SQL> INSERT INTO product_order VALUES ("Product 4", "GA", "15-JUL-03", 8); 1 row created. SQL> INSERT INTO product_order VALUES ("Product 5", "LB", "15-JUL-03", 20); 1 row created. SQL> INSERT INTO product_order VALUES ("Product 6", "CA", "16-JUL-03", 5); 1 row created. SQL> INSERT INTO product_order VALUES ("Product 7", "CA", "17-JUL-03", 1); 1 row created. SQL> SQL> SQL> SQL> ALTER TABLE product_order ADD CONSTRAINT reasonable_date CHECK(
2 order_date IS NOT NULL 3 AND 4 TO_CHAR(order_date, "YYYY-MM-DD") >= "2000-06-30" 5 );
Table altered. SQL> SQL> SQL> drop table product_order; Table dropped.</source>
BETWEEN for date type value
<source lang="sql">
SQL> SQL> CREATE TABLE titles(
2 title_id CHAR(3) NOT NULL, 3 title_name VARCHAR(40) NOT NULL, 4 type VARCHAR(10) NULL , 5 pub_id CHAR(3) NOT NULL, 6 pages INTEGER NULL , 7 price DECIMAL(5,2) NULL , 8 sales INTEGER NULL , 9 pubdate DATE NULL , 10 contract SMALLINT NOT NULL 11 );
Table created. SQL> SQL> SQL> SQL> SQL> INSERT INTO titles VALUES("T01","Java","history","P01",111,21.99,566,DATE "2000-08-01",1); 1 row created. SQL> INSERT INTO titles VALUES("T02","Oracle","history","P03", 114,19.95,9566,DATE "1998-04-01",1); 1 row created. SQL> INSERT INTO titles VALUES("T03","SQL","computer","P02", 122,39.95,25667,DATE "2000-09-01",1); 1 row created. SQL> INSERT INTO titles VALUES("T04","C++","psychology","P04", 511,12.99,13001,DATE "1999-05-31",1); 1 row created. SQL> INSERT INTO titles VALUES("T05","Python","psychology","P04", 101,6.95,201440,DATE "2001-01-01",1); 1 row created. SQL> INSERT INTO titles VALUES("T06","JavaScript","biography","P01", 173,19.95,11320,DATE "2000-07-31",1); 1 row created. SQL> INSERT INTO titles VALUES("T07","LINQ","biography","P03", 331,23.95,1500200,DATE "1999-10-01",1); 1 row created. SQL> INSERT INTO titles VALUES("T08","C#","children","P04", 861,10.00,4095,DATE "2001-06-01",1); 1 row created. SQL> INSERT INTO titles VALUES("T09","SQL Server","children","P04", 212,13.95,5000,DATE "2002-05-31",1); 1 row created. SQL> INSERT INTO titles VALUES("T10","AJAX","biography","P01", NULL,NULL,NULL,NULL,0); 1 row created. SQL> INSERT INTO titles VALUES("T11","VB","psychology","P04", 821,7.99,94123,DATE "2000-11-30",1); 1 row created. SQL> INSERT INTO titles VALUES("T12","Office","biography","P01", 507,12.99,100001,DATE "2000-08-31",1); 1 row created. SQL> INSERT INTO titles VALUES("T13","VBA","history","P03", 812,29.99,10467,DATE "1999-05-31",1); 1 row created. SQL> SQL> SQL> SQL> SELECT title_id, pubdate
2 FROM titles 3 WHERE pubdate BETWEEN DATE "2000-01-01" 4 AND DATE "2000-12-31";
TIT PUBDATE --- --------- T01 01-AUG-00 T03 01-SEP-00 T06 31-JUL-00 T11 30-NOV-00 T12 31-AUG-00 SQL> SQL> drop table titles; Table dropped. SQL></source>
Call SYSDATE in insert statement
<source lang="sql">
SQL> SQL> create table myTable (
2 event_name varchar2( 100 ), 3 event_date date );
Table created. SQL> SQL> SQL> SQL> insert into myTable ( event_name, event_date )
2 values ( "Created DATE Sample code", SYSDATE );
1 row created. SQL> SQL> SQL> select * from myTable;
EVENT_NAME EVENT_DAT
Created DATE Sample code 03-JUN-07 SQL> drop table myTable; Table dropped.</source>
Cast string to date in where clause
<source lang="sql">
SQL> SQL> create table employees(
2 empno NUMBER(4) 3 , ename VARCHAR2(8) 4 , init VARCHAR2(5) 5 , job VARCHAR2(8) 6 , mgr NUMBER(4) 7 , bdate DATE 8 , msal NUMBER(6,2) 9 , comm NUMBER(6,2) 10 , deptno NUMBER(2) ) ;
Table created. SQL> SQL> SQL> insert into employees values(1,"Jason", "N", "TRAINER", 2, date "1965-12-18", 800 , NULL, 10); 1 row created. SQL> insert into employees values(2,"Jerry", "J", "SALESREP",3, date "1966-11-19", 1600, 300, 10); 1 row created. SQL> insert into employees values(3,"Jord", "T" , "SALESREP",4, date "1967-10-21", 1700, 500, 20); 1 row created. SQL> insert into employees values(4,"Mary", "J", "MANAGER", 5, date "1968-09-22", 1800, NULL, 20); 1 row created. SQL> insert into employees values(5,"Joe", "P", "SALESREP",6, date "1969-08-23", 1900, 1400, 30); 1 row created. SQL> insert into employees values(6,"Black", "R", "MANAGER", 7, date "1970-07-24", 2000, NULL, 30); 1 row created. SQL> insert into employees values(7,"Red", "A", "MANAGER", 8, date "1971-06-25", 2100, NULL, 40); 1 row created. SQL> insert into employees values(8,"White", "S", "TRAINER", 9, date "1972-05-26", 2200, NULL, 40); 1 row created. SQL> insert into employees values(9,"Yellow", "C", "DIRECTOR",10, date "1973-04-27", 2300, NULL, 20); 1 row created. SQL> insert into employees values(10,"Pink", "J", "SALESREP",null,date "1974-03-28", 2400, 0, 30); 1 row created. SQL> SQL> select e.ename as employee
2 , m.ename as manager 3 from employees m 4 , employees e 5 where e.mgr = m.empno 6 and e.bdate > date "1965-01-01" 7 /
EMPLOYEE MANAGER
--------
Jason Jerry Jerry Jord Jord Mary Mary Joe Joe Black Black Red Red White White Yellow Yellow Pink 9 rows selected. SQL> SQL> SQL> drop table employees; Table dropped. SQL> SQL></source>
Check Date format before inserting
<source lang="sql">
SQL> SQL> create table myTable (
2 event_name varchar2( 100 ), 3 event_date date );
Table created. SQL> SQL> SQL> show parameters nls_date_format
NAME_COL_PLUS_SHOW_PARAM TYPE
-----------
VALUE_COL_PLUS_SHOW_PARAM
nls_date_format string SQL> insert into myTable ( event_name, event_date )
2 values ( "Created an Oracle9i DATE value", DATE "2001-10-11" ) 3 /
1 row created. SQL> select * from myTable;
/
EVENT_NAME EVENT_DAT
Created an Oracle9i DATE value 11-OCT-01 SQL> drop table myTable; Table dropped.</source>
Combining TO_CHAR() and TO_DATE() Calls
The following query combines TO_CHAR() and TO_DATE() to view just the time part of a datetime.
<source lang="sql">
SQL> SELECT TO_CHAR(TO_DATE("05-FEB-1968 19:32:36",
2 "DD-MON-YYYY HH24:MI:SS"), "HH24:MI:SS") 3 FROM dual;
TO_CHAR(
19:32:36 SQL></source>
Compare date type value in where clause
<source lang="sql">
SQL> SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name 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> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SELECT * FROM employee WHERE start_date = "15-JAN-06"; no rows selected SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL></source>
Converting a DATE to Another Language
<source lang="sql">
SQL> SQL> SQL> SELECT TO_CHAR(SYSDATE,"MONTH DD YY","NLS_DATE_LANGUAGE=german")
2 "German Date" from dual;
German Date
JUNI 02 07 SQL> SQL></source>
Date calculation
<source lang="sql">
SQL> SQL> CREATE TABLE employee
2 (employee_id NUMBER(7), 3 last_name VARCHAR2(25), 4 first_name VARCHAR2(25), 5 userid VARCHAR2(8), 6 start_date DATE, 7 comments VARCHAR2(255), 8 manager_id NUMBER(7), 9 title VARCHAR2(25), 10 department_id NUMBER(7), 11 salary NUMBER(11, 2), 12 commission_pct NUMBER(4, 2) 13 );
Table created. SQL> SQL> INSERT INTO employee VALUES (1, "V", "Ben", "cv",to_date("03-MAR-90 8:30", "dd-mon-yy hh24:mi"),NULL, NULL, "PRESIDENT", 50, 2500, NULL); 1 row created. SQL> INSERT INTO employee VALUES (2, "N", "Haidy", "ln", "08-MAR-90", NULL,1, "VP, OPERATIONS", 41, 1450, NULL); 1 row created. SQL> INSERT INTO employee VALUES (3, "N", "Molly", "mn", "17-JUN-91",NULL, 1, "VP, SALES", 31, 1400, NULL); 1 row created. SQL> INSERT INTO employee VALUES (4, "S", "Mark", "mq", "07-APR-90",NULL, 1, "VP, FINANCE", 10, 1450, NULL); 1 row created. SQL> INSERT INTO employee VALUES (5, "R", "AUDRY", "ar", "04-MAR-90",NULL, 1, "VP, ADMINISTRATION", 50, 1550, NULL); 1 row created. SQL> INSERT INTO employee VALUES (6, "U", "MOLLY", "mu", "18-JAN-91",NULL, 2, "WAREHOUSE MANAGER", 41, 1200, NULL); 1 row created. SQL> INSERT INTO employee VALUES (7, "M", "ROBERTA", "rm", "14-MAY-90",NULL, 2, "WAREHOUSE MANAGER", 41, 1250, NULL); 1 row created. SQL> INSERT INTO employee VALUES (8, "B", "BEN", "ry", "07-APR-90", NULL, 2,"WAREHOUSE MANAGER", 41, 1100, NULL); 1 row created. SQL> INSERT INTO employee VALUES (9, "C", "Jane", "ac", "09-FEB-92",NULL, 2, "WAREHOUSE MANAGER", 41, 1300, NULL); 1 row created. SQL> INSERT INTO employee VALUES (10, "H", "Mart", "mh", "27-FEB-91", NULL, 2,"WAREHOUSE MANAGER", 41, 1307, NULL); 1 row created. SQL> SQL> SQL> SQL> SELECT employee_id, last_name, first_name,start_date
2 FROM employee
3 WHERE start_date BETWEEN "08-MAR-90" AND TO_DATE("08-MAR-90") + 1 - .000011574;
EMPLOYEE_ID LAST_NAME FIRST_NAME START_DAT
------------------------- ------------------------- ---------
2 N Haidy 08-MAR-90
SQL> SQL> drop table employee; Table dropped. SQL></source>
Default time field
<source lang="sql">
SQL> SQL> SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name 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> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SELECT id, TO_CHAR(start_date, "DD-MON-YYYY HH24:MI:SS")
2 FROM employee;
ID TO_CHAR(START_DATE,"
--------------------
01 25-JUL-1996 00:00:00 02 21-MAR-1976 00:00:00 03 12-DEC-1978 00:00:00 04 24-OCT-1982 00:00:00 05 15-JAN-1984 00:00:00 06 30-JUL-1987 00:00:00 07 31-DEC-1990 00:00:00 08 17-SEP-1996 00:00:00 8 rows selected. SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL></source>
Greater than a date
<source lang="sql">
SQL> SQL> CREATE TABLE titles(
2 title_id CHAR(3) NOT NULL, 3 title_name VARCHAR(40) NOT NULL, 4 type VARCHAR(10) NULL , 5 pub_id CHAR(3) NOT NULL, 6 pages INTEGER NULL , 7 price DECIMAL(5,2) NULL , 8 sales INTEGER NULL , 9 pubdate DATE NULL , 10 contract SMALLINT NOT NULL 11 );
Table created. SQL> SQL> SQL> SQL> SQL> INSERT INTO titles VALUES("T01","Java","history","P01",111,21.99,566,DATE "2000-08-01",1); 1 row created. SQL> INSERT INTO titles VALUES("T02","Oracle","history","P03", 114,19.95,9566,DATE "1998-04-01",1); 1 row created. SQL> INSERT INTO titles VALUES("T03","SQL","computer","P02", 122,39.95,25667,DATE "2000-09-01",1); 1 row created. SQL> INSERT INTO titles VALUES("T04","C++","psychology","P04", 511,12.99,13001,DATE "1999-05-31",1); 1 row created. SQL> INSERT INTO titles VALUES("T05","Python","psychology","P04", 101,6.95,201440,DATE "2001-01-01",1); 1 row created. SQL> INSERT INTO titles VALUES("T06","JavaScript","biography","P01", 173,19.95,11320,DATE "2000-07-31",1); 1 row created. SQL> INSERT INTO titles VALUES("T07","LINQ","biography","P03", 331,23.95,1500200,DATE "1999-10-01",1); 1 row created. SQL> INSERT INTO titles VALUES("T08","C#","children","P04", 861,10.00,4095,DATE "2001-06-01",1); 1 row created. SQL> INSERT INTO titles VALUES("T09","SQL Server","children","P04", 212,13.95,5000,DATE "2002-05-31",1); 1 row created. SQL> INSERT INTO titles VALUES("T10","AJAX","biography","P01", NULL,NULL,NULL,NULL,0); 1 row created. SQL> INSERT INTO titles VALUES("T11","VB","psychology","P04", 821,7.99,94123,DATE "2000-11-30",1); 1 row created. SQL> INSERT INTO titles VALUES("T12","Office","biography","P01", 507,12.99,100001,DATE "2000-08-31",1); 1 row created. SQL> INSERT INTO titles VALUES("T13","VBA","history","P03", 812,29.99,10467,DATE "1999-05-31",1); 1 row created. SQL> SQL> SQL> SQL> SQL> SELECT title_name, pubdate
2 FROM titles 3 WHERE pubdate >= DATE "2001-01-01";
TITLE_NAME PUBDATE
---------
Python 01-JAN-01 C# 01-JUN-01 SQL Server 31-MAY-02 SQL> SQL> drop table titles; Table dropped. SQL> SQL></source>
Process and store a specific date and time.
An example of a datetime is 7:15:30 PM on October 10, 2005.
You store a datetime using the DATE type.
The DATE type stores the century, all four digits of a year, the month, the day, the hour (in 24-hour format), the minute, and the second.
10. 6. Date 10. 6. 1. Process and store a specific date and time. 10. 6. 2. <A href="/Tutorial/Oracle/0200__SQL-Data-Types/UsetheDATEkeywordtosupplyadateliteraltothedatabase.htm">Use the DATE keyword to supply a date literal to the database.</a> 10. 6. 3. <A href="/Tutorial/Oracle/0200__SQL-Data-Types/Caststringtodateinwhereclause.htm">Cast string to date in where clause</a> 10. 6. 4. <A href="/Tutorial/Oracle/0200__SQL-Data-Types/SettingtheDefaultDateFormat.htm">Setting the Default Date Format</a> 10. 6. 5. <A href="/Tutorial/Oracle/0200__SQL-Data-Types/StoringandRetrievingDates.htm">Storing and Retrieving Dates</a> 10. 6. 6. <A href="/Tutorial/Oracle/0200__SQL-Data-Types/Defaulttimefield.htm">Default time field</a> 10. 6. 7. <A href="/Tutorial/Oracle/0200__SQL-Data-Types/CombiningTOCHARandTODATECalls.htm">Combining TO_CHAR() and TO_DATE() Calls</a> 10. 6. 8. <A href="/Tutorial/Oracle/0200__SQL-Data-Types/ConvertingaDATEtoAnotherLanguage.htm">Converting a DATE to Another Language</a> 10. 6. 9. <A href="/Tutorial/Oracle/0200__SQL-Data-Types/Comparedatetypevalueinwhereclause.htm">Compare date type value in where clause</a> 10. 6. 10. <A href="/Tutorial/Oracle/0200__SQL-Data-Types/QueryforNULLDatetypedata.htm">Query for NULL Date type data</a> 10. 6. 11. <A href="/Tutorial/Oracle/0200__SQL-Data-Types/TRUNCStartdate.htm">TRUNC(Start_date)</a> 10. 6. 12. <A href="/Tutorial/Oracle/0200__SQL-Data-Types/TOCHARstartdateMMDDYYYYHH24MI.htm">TO_CHAR(start_date, "MM-DD-YYYY HH24:MI")</a> 10. 6. 13. <A href="/Tutorial/Oracle/0200__SQL-Data-Types/CallSYSDATEininsertstatement.htm">Call SYSDATE in insert statement</a> 10. 6. 14. <A href="/Tutorial/Oracle/0200__SQL-Data-Types/CheckDateformatbeforeinserting.htm">Check Date format before inserting</a> 10. 6. 15. <A href="/Tutorial/Oracle/0200__SQL-Data-Types/Addconditionconstrainttodatetypecolumn.htm">Add condition constraint to date type column</a> 10. 6. 16. <A href="/Tutorial/Oracle/0200__SQL-Data-Types/Greaterthanadate.htm">Greater than a date</a> 10. 6. 17. <A href="/Tutorial/Oracle/0200__SQL-Data-Types/BETWEENfordatetypevalue.htm">BETWEEN for date type value</a> 10. 6. 18. <A href="/Tutorial/Oracle/0200__SQL-Data-Types/Datecalculation.htm">Date calculation</a>
Query for NULL Date type data
<source lang="sql">
SQL> SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name 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> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SELECT * FROM employee WHERE start_date IS NULL; no rows selected SQL> SELECT * FROM employee WHERE start_date IS NOT NULL;
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL></source>
Setting the Default Date Format
The default date format is specified in the NLS_DATE_FORMAT database parameter.
You can change the setting by setting this parameter"s value init.ora or spfile.ora file.
init.ora or spfile.ora file is read when the database is started.
You can set NLS_DATE_FORMAT using an ALTER SYSTEM command.
You can set the NLS_DATE_FORMAT parameter for your own session using SQL*Plus using ALTER SESSION.
A session is started when you connect to a database and is ended when you disconnect.
The following ALTER SESSION statement sets NLS_DATE_FORMAT to MONTH-DD-YYYY.
<source lang="sql">
ALTER SESSION SET NLS_DATE_FORMAT = "MONTH-DD-YYYY";</source>
Storing and Retrieving Dates
By default, you can supply a date with the format DD-MON-YYYY to the database, where:
- DD is a two-digit day such as 05
- MON is the first three letters of the month such as FEB
- YYYY is a four-digit year such as 1968
<source lang="sql">
SQL> SQL> SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) , 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name 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> -- prepare data SQL> insert into Employee(Start_Date) values ("05-FEB-2005")
2 /
SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped.</source>
TO_CHAR(start_date, "MM-DD-YYYY HH24:MI")
<source lang="sql">
SQL> SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name 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> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SELECT TO_CHAR(start_date, "MM-DD-YYYY HH24:MI") "Last"
2 FROM employee;
Last
07-25-1996 00:00 03-21-1976 00:00 12-12-1978 00:00 10-24-1982 00:00 01-15-1984 00:00 07-30-1987 00:00 12-31-1990 00:00 09-17-1996 00:00 8 rows selected. SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL></source>
TRUNC(Start_date)
<source lang="sql">
SQL> SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name 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> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SELECT * FROM employee WHERE TRUNC(Start_date) = "26-MAR-04"; no rows selected SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL></source>
Use the DATE keyword to supply a date literal to the database.
Your date must use the ANSI standard date format YYYY-MM-DD, where:
- YYYY is a four-digit year
- MM is a two-digit month from 1 to 12
- DD is a two-digit day
<source lang="sql">
SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name 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> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", Date "1972-10-25", to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---------- ---------- --------- --------- ---------- ---------- ---------------
08 James Cat 25-OCT-72 15-APR-02 1232.78 Vancouver Tester SQL> SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped.</source>
