Oracle PL/SQL Tutorial/Date Timestamp Functions/TO DATE
Содержание
- 1 Birthday before 1940
- 2 Catch exception from to_date function
- 3 DBMS_OUTPUT.PUT_LINE(TO_DATE ("1/1"));
- 4 DBMS_OUTPUT.PUT_LINE(TO_DATE ("1/1/1"))
- 5 DBMS_OUTPUT.PUT_LINE(TO_DATE ("12-APR-09"));
- 6 DBMS_OUTPUT.PUT_LINE(TO_DATE ("19991205"));
- 7 DBMS_OUTPUT.PUT_LINE(TO_DATE ("6/1996"));
- 8 Specifying a Datetime Format for TO_DATE() function
- 9 Specifying Times
- 10 TO_CHAR(TO_DATE("04-JUL-15", "DD-MON-RR"), "DD-MON-YYYY")
- 11 TO_DATE() converts the strings 04-JUL-2006 to the date July 4, 2006
- 12 TO_DATE function to convert from characters to dates explicitly
- 13 TO_DATE() with INSERT statement
- 14 TO_DATE(x[, format]) converts the x string to a datetime
- 15 Using the RR Format
- 16 Using the YY Format
Birthday before 1940
SQL>
SQL>
SQL>
SQL> CREATE TABLE employee (
2 id number,
3 name varchar(100),
4 birth_date date,
5 gender varchar2(30) );
Table created.
SQL>
SQL> INSERT INTO employee (id,name,birth_date,gender )VALUES (100,"J",to_date("19230823", "YYYYMMDD"),"MALE" );
1 row created.
SQL>
SQL> SELECT name
2 FROM employee
3 WHERE birth_date < to_date("19400101", "YYYYMMDD")
4 ORDER BY name;
NAME
----------------------------------------------------------------------
J
1 row selected.
SQL>
SQL>
SQL> drop table employee;
Table dropped.
SQL>
Catch exception from to_date function
SQL>
SQL>
SQL> DECLARE
2 bad_date EXCEPTION;
3 PRAGMA EXCEPTION_INIT (bad_date, -01843);
4 BEGIN
5 DBMS_OUTPUT.PUT_LINE (TO_DATE ("13/13/99", "MM/DD/YY"));
6 EXCEPTION
7 WHEN bad_date
8 THEN
9 DBMS_OUTPUT.PUT_LINE("Just twelve months in a year...");
10 END;
11 /
Just twelve months in a year...
PL/SQL procedure successfully completed.
SQL>
DBMS_OUTPUT.PUT_LINE(TO_DATE ("1/1"));
SQL>
SQL>
SQL> BEGIN
2
3 DBMS_OUTPUT.PUT_LINE(TO_DATE ("1/1"));
4
5 END;
6 /
END;
*
ERROR at line 5:
ORA-01843: not a valid month
ORA-06512: at line 3
SQL>
DBMS_OUTPUT.PUT_LINE(TO_DATE ("1/1/1"))
SQL>
SQL>
SQL> BEGIN
2
3 DBMS_OUTPUT.PUT_LINE(TO_DATE ("1/1/1"));
4
5 END;
6 /
END;
*
ERROR at line 5:
ORA-01843: not a valid month
ORA-06512: at line 3
SQL>
SQL>
DBMS_OUTPUT.PUT_LINE(TO_DATE ("12-APR-09"));
SQL>
SQL>
SQL> BEGIN
2
3 DBMS_OUTPUT.PUT_LINE(TO_DATE ("12-APR-09"));
4
5 END;
6 /
12-APR-09
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
DBMS_OUTPUT.PUT_LINE(TO_DATE ("19991205"));
SQL>
SQL>
SQL> BEGIN
2
3 DBMS_OUTPUT.PUT_LINE(TO_DATE ("19991205"));
4
5 END;
6 /
END;
*
ERROR at line 5:
ORA-01861: literal does not match format string
ORA-06512: at line 3
SQL>
SQL>
DBMS_OUTPUT.PUT_LINE(TO_DATE ("6/1996"));
SQL>
SQL> BEGIN
2
3 DBMS_OUTPUT.PUT_LINE(TO_DATE ("6/1996"));
4
5 END;
6 /
END;
*
ERROR at line 5:
ORA-01843: not a valid month
ORA-06512: at line 3
SQL>
SQL>
SQL>
Specifying a Datetime Format for TO_DATE() function
Aspect Parameter Description Example Century CC Two-digit century. 21 Century SCC Two-digit century with a negative sign (C) for B.C. -10 Quarter Q One-digit quarter of the year. 1 Year YYYY All four digits of the year. 2006 Year IYYY All four digits of the ISO year. 2006 Year RRRR All four digits of the rounded year, which depends on the current year. 2006 Year SYYYY All four digits of the year with a negative sign (C) for B.C. C1001 Year Y,YYY All four digits of the year with a comma. 2,006 Year YYY Last three digits of the year. 006 Year IYY Last three digits of the ISO year. 006 Year YY Last two digits of the year. 06 Year IY Last two digits of the ISO year. 06 Year RR Last two digits of the rounded year, which depends on the current year. 06 Year Y Last digit of the year. 6 Year I Last digit of the ISO year. 6 Year YEAR Name of the year in uppercase. TWO THOUSAND-SIX Year Year Name of the year with the first letter in uppercase. Two Thousand-Six Month MM Two-digit month of the year. 01 Month MONTH Full name of the month in uppercase, right-padded with spaces to a total length of nine characters. JANUARY Month Month Full name of the month with first letter in uppercase, right-padded with spaces to a total length of nine characters. January Month MON First three letters of the name of the month in uppercase. JAN Month Mon First three letters of the name of the month with the first letter in uppercase. Jan Month RM Roman numeral month. The Roman numeral month for the fourth month (April) is IV. Week WW Two-digit week of the year. 02 Week IW Two-digit ISO week of the year. 02 Week W One-digit week of the month. 2 Day DDD Three-digit day of the year. 103 Day DD Two-digit day of the month. 31 Day D One-digit day of the week. 5 Day DAY Full name of the day in uppercase. SATURDAY Day Day Full name of the day with the first letter in uppercase. Saturday Day DY First three letters of the name of the day in uppercase. SAT Day Dy First three letters of the name of the day with the first letter in uppercase. Sat Day J Julian day-the number of days that have passed since January 1, 4713 B.C. 2439892 Hour HH24 Two-digit hour in 24-hour format. 23 Hour HH Two-digit hour in 12-hour format. 11 Minute MI Two-digit minute. 57 Second SS Two-digit second. 45 Second FF[1..9] Fractional seconds with an optional number of digits to the right of the decimal point. Only applies timestamps,When dealing with 0.123456789 seconds, FF3 would round to 0.123. Second SSSSS Number of seconds past 12 a.m. 46748 Second MS Millisecond (millionths of a second). 100 Second CS Centisecond (hundredths of a second). 10 Separators -/,.;: "text" Characters that allow you to separate the aspects of a date and time. You can supply freeform text in quotes as a separator. When dealing with the date December 13, 1969, DD-MM-YYYY would produce 12-13-1969 and DD/MM/YYYY would produce 12/13/1969 Suffixes AM or PM AM or PM as appropriate. AM Suffixes A.M. or P.M. A.M. or P.M. as appropriate. P.M. Suffixes AD or BC AD or BC as appropriate. AD Suffixes A.D. or B.C. A.D. or B.C. as appropriate. B.C. Suffixes TH Suffix to a number. You can make the suffix uppercase by specifying the numeric format in uppercase and vice versa for lowercase.When dealing with a day number of 28, ddTH would produce 28th and DDTH would produce 28TH Suffixes SP Number is spelled out.When dealing with a day number of 28, DDSP would produce TWENTY-EIGHT and ddSP would produce twenty-eight Suffixes SPTH Combination of TH and SP.When dealing with a day number of 28, DDSPTH would produce TWENTY-EIGHTH and ddSPTH would produce twenty-eighth Era EE Full era name for Japanese Imperial, ROC Official, and Thai Buddha calendars. No example Era E Abbreviated era name. No example Time zones TZH Time zone hour. 12 Time zones TZM Time zone minute. 30 Time zones TZR Time zone region. PST Time zones TZD Time zone with daylight savings information. No example
Quote from:
Oracle Database 10g SQL (Osborne ORACLE Press Series) (Paperback)
# Paperback: 608 pages
# Publisher: McGraw-Hill Osborne Media; 1st edition (February 20, 2004)
# Language: English
# ISBN-10: 0072229810
# ISBN-13: 978-0072229813
Specifying Times
The time part of your datetime defaults to 00:00:00 A.M.
HH24:MI:SS, where:
- HH24 is a two-digit hour in 24-hour format from 00 to 23.
- MI is a two-digit minute from 00 to 59.
- SS is a two-digit second from 00 to 59.
SQL> Select TO_DATE("05-FEB-1968 19:32:36", "DD-MON-YYYY HH24:MI:SS") from dual;
TO_DATE("
---------
05-FEB-68
TO_CHAR(TO_DATE("04-JUL-15", "DD-MON-RR"), "DD-MON-YYYY")
SQL> SELECT
2 TO_CHAR(TO_DATE("04-JUL-15", "DD-MON-RR"), "DD-MON-YYYY"),
3 TO_CHAR(TO_DATE("04-JUL-55", "DD-MON-RR"), "DD-MON-YYYY")
4 FROM dual;
TO_CHAR(TO_ TO_CHAR(TO_
----------- -----------
04-JUL-2015 04-JUL-1955
SQL>
SQL>
TO_DATE() converts the strings 04-JUL-2006 to the date July 4, 2006
SQL>
SQL> SELECT TO_DATE("04-JUL-2006"), TO_DATE("04-JUL-06") FROM dual;
TO_DATE(" TO_DATE("
--------- ---------
04-JUL-06 04-JUL-06
SQL>
TO_DATE function to convert from characters to dates explicitly
SQL>
SQL> SELECT TO_CHAR(LAST_DAY(TO_DATE("23SEP2006","ddMONyyyy"))) FROM dual;
TO_CHAR(L
---------
30-SEP-06
SQL>
TO_DATE() with INSERT statement
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>
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
TO_DATE(x[, format]) converts the x string to a datetime
You can provide an optional format string to indicate the format of x.
The default format is DD-MON-YYYY or DD-MON-YY.
13. 21. TO_DATE 13. 21. 1. <A href="/Tutorial/Oracle/0260__Date-Timestamp-Functions/SpecifyingaDatetimeFormatforTODATEfunction.htm">Specifying a Datetime Format for TO_DATE() function</a> 13. 21. 2. <A href="/Tutorial/Oracle/0260__Date-Timestamp-Functions/UsingtheYYFormat.htm">Using the YY Format</a> 13. 21. 3. <A href="/Tutorial/Oracle/0260__Date-Timestamp-Functions/UsingtheRRFormat.htm">Using the RR Format</a> 13. 21. 4. <A href="/Tutorial/Oracle/0260__Date-Timestamp-Functions/TOCHARTODATE04JUL15DDMONRRDDMONYYYY.htm">TO_CHAR(TO_DATE("04-JUL-15", "DD-MON-RR"), "DD-MON-YYYY")</a> 13. 21. 5. <A href="/Tutorial/Oracle/0260__Date-Timestamp-Functions/TODATEfunctiontoconvertfromcharacterstodatesexplicitly.htm">TO_DATE function to convert from characters to dates explicitly</a> 13. 21. 6. TO_DATE(x[, format]) converts the x string to a datetime 13. 21. 7. <A href="/Tutorial/Oracle/0260__Date-Timestamp-Functions/TODATEconvertsthestrings04JUL2006tothedateJuly42006.htm">TO_DATE() converts the strings 04-JUL-2006 to the date July 4, 2006</a> 13. 21. 8. <A href="/Tutorial/Oracle/0260__Date-Timestamp-Functions/SpecifyingTimes.htm">Specifying Times</a> 13. 21. 9. <A href="/Tutorial/Oracle/0260__Date-Timestamp-Functions/TODATEwithINSERTstatement.htm">TO_DATE() with INSERT statement</a> 13. 21. 10. <A href="/Tutorial/Oracle/0260__Date-Timestamp-Functions/DBMSOUTPUTPUTLINETODATE11.htm">DBMS_OUTPUT.PUT_LINE(TO_DATE ("1/1"));</a> 13. 21. 11. <A href="/Tutorial/Oracle/0260__Date-Timestamp-Functions/DBMSOUTPUTPUTLINETODATE61996.htm">DBMS_OUTPUT.PUT_LINE(TO_DATE ("6/1996"));</a> 13. 21. 12. <A href="/Tutorial/Oracle/0260__Date-Timestamp-Functions/DBMSOUTPUTPUTLINETODATE12APR09.htm">DBMS_OUTPUT.PUT_LINE(TO_DATE ("12-APR-09"));</a> 13. 21. 13. <A href="/Tutorial/Oracle/0260__Date-Timestamp-Functions/DBMSOUTPUTPUTLINETODATE19991205.htm">DBMS_OUTPUT.PUT_LINE(TO_DATE ("19991205"));</a> 13. 21. 14. <A href="/Tutorial/Oracle/0260__Date-Timestamp-Functions/DBMSOUTPUTPUTLINETODATE111.htm">DBMS_OUTPUT.PUT_LINE(TO_DATE ("1/1/1"))</a> 13. 21. 15. <A href="/Tutorial/Oracle/0260__Date-Timestamp-Functions/Birthdaybefore1940.htm">Birthday before 1940</a> 13. 21. 16. <A href="/Tutorial/Oracle/0260__Date-Timestamp-Functions/Catchexceptionfromtodatefunction.htm">Catch exception from to_date function</a>
Using the RR Format
If your date format is RR and you supply the last two digits of a year, the first two digits of your year are determined using the two-digit year you supply (your supplied year) and the last two digits of the present date on your database server (the present year). The rules used to determine the century of your supplied year are as follows:
- Rule 1 If your supplied year is between 00 and 49 and the present year is between 00 and 49, the century is the same as the present century. Therefore, the first two digits of your supplied year are set to the first two digits of the present year. For example, if your supplied year is 15 and the present year is 2005, your supplied year is set to 2015.
- Rule 2 If your supplied year is between 50 and 99 and the present year is between 00 and 49, the century is the present century minus 1. Therefore, the first two digits of your supplied year are set to the present year"s first two digits minus 1. For example, if your supplied year is 75 and the present year is 2005, your supplied year is set to 1975.
- Rule 3 If your supplied year is between 00 and 49 and the present year is between 50 and 99, the century is the present century plus 1. Therefore, the first two digits of your supplied year are set to the present year"s first two digits plus 1. For example, if your supplied year is 15 and the present year is 2075, your supplied year is set to 2115.
- Rule 4 If your supplied year is between 50 and 99 and the present year is between 50 and 99, the century is the same as the present century. Therefore, the first two digits of your supplied year are set to the first two digits of the present year. For example, if your supplied year is 55 and the present year is 2075, your supplied year is set to 2055.
Quote from:
Oracle Database 10g SQL (Osborne ORACLE Press Series) (Paperback)
# Paperback: 608 pages
# Publisher: McGraw-Hill Osborne Media; 1st edition (February 20, 2004)
# Language: English
# ISBN-10: 0072229810
# ISBN-13: 978-0072229813
SQL>
SQL> SELECT
2 TO_CHAR(TO_DATE("04-JUL-15", "DD-MON-RR"), "DD-MON-YYYY"),
3 TO_CHAR(TO_DATE("04-JUL-75", "DD-MON-RR"), "DD-MON-YYYY")
4 FROM dual;
TO_CHAR(TO_ TO_CHAR(TO_
----------- -----------
04-JUL-2015 04-JUL-1975
SQL>
Using the YY Format
If you use the YYYY format but only supply a two-digit date, the date is interpreted using the YY format.
In this case, the century for your year is assumed to be the same as the present century currently set on your database server.
which means, first two digits of your supplied year are set to the first two digits of the present year.
SQL> SELECT
2 TO_CHAR(TO_DATE("04-JUL-25", "DD-MON-YY"), "DD-MON-YYYY"),
3 TO_CHAR(TO_DATE("04-JUL-85", "DD-MON-YY"), "DD-MON-YYYY")
4 FROM dual;
TO_CHAR(TO_ TO_CHAR(TO_
----------- -----------
04-JUL-2025 04-JUL-2085
SQL>