Oracle PL/SQL Tutorial/SQL Data Types/Date Format
Содержание
- 1 AD or BC: AD or BC as appropriate
- 2 A.D. or B.C.: A.D. or B.C. as appropriate (2)
- 3 AM or PM: AM or PM as appropriate
- 4 A.M. or P.M.: A.M. or P.M. as appropriate (2)
- 5 By default, the database outputs dates in the format DD-MON-YY
- 6 CC: Two-digit century
- 7 Datetime Formatting Parameters for TO_CHAR() function
- 8 DAY: Full name of the day in uppercase
- 9 Day: Full name of the day with the first letter in uppercase
- 10 DAY MON, YY AD
- 11 DDD: Three-digit day of the year
- 12 DDSPTH "of" MONTH, YEAR A.D.
- 13 DD:Two-digit day of the month
- 14 D: One-digit day of the week
- 15 DY: First three letters of the name of the day in uppercase
- 16 Dy: First three letters of the name of the day with the first letter in uppercase
- 17 HH24: Two-digit hour in 24-hour format
- 18 HH: Two-digit hour in 12-hour format
- 19 I: Last digit of the ISO year
- 20 Insert Date value with default format
- 21 IW: Two-digit ISO week of the year
- 22 IY: Last two digits of the ISO year
- 23 IYY: Last three digits of the ISO year
- 24 IYYY: All four digits of the ISO year
- 25 J: Julian day-the number of days that have passed since January 1, 4713 B.C.
- 26 MI: Two-digit minute
- 27 MM: Two-digit month of the year
- 28 MON: First three letters of the name of the month in uppercase
- 29 Mon: First three letters of the name of the month with the first letter in uppercase
- 30 MONTH: Full name of the month in uppercase, right-padded with spaces to a total length of nine characters
- 31 Month: Full name of the month with first letter in uppercase, right-padded with spaces to a total length of nine characters
- 32 Q: One-digit quarter of the year
- 33 RM:Roman numeral month.
- 34 RR: Last two digits of the rounded year, which depends on the current year
- 35 RRRR: All four digits of the rounded year, which depends on the current year
- 36 SCC: Two-digit century with a negative sign (-) for B.C.
- 37 SELECT TO_CHAR(ADD_MONTHS(TO_DATE("01-JAN-15:26","DD-MON-YYYY HH24:MI:SS"), 2), "DD-MON-YYYY HH24:MI:SS") FROM dual;
- 38 SELECT TO_CHAR(SYSDATE, "DD/MM/YYYY")
- 39 SELECT TO_CHAR(SYSDATE, "PM")
- 40 SELECT TO_CHAR(SYSDATE, "P.M.") (2)
- 41 SELECT TO_CHAR(TO_DATE("05-FEB-1968"), "MONTH DD, YYYY")
- 42 SS: Two-digit second
- 43 SYYYY: All four digits of the year with a negative sign (-) for B.C.
- 44 -/,.;: "text"
- 45 TO_CHAR(SYSDATE, "BC")
- 46 TO_CHAR(SYSDATE, "B.C.") (2)
- 47 W: One-digit week of the month
- 48 WW: Two-digit week of the year
- 49 YEAR: Name of the year in uppercase
- 50 Year: Name of the year with the first letter in uppercase
- 51 Y: Last digit of the year
- 52 YY: Last two digits of the year
- 53 YYY: Last three digits of the year
- 54 YYYY: All four digits of the year
- 55 Y,YYY: All four digits of the year with a comma
AD or BC: AD or BC as appropriate
<source lang="sql">
SQL> SQL> SELECT TO_CHAR(SYSDATE, "AD")
2 FROM dual;
TO -- AD SQL></source>
A.D. or B.C.: A.D. or B.C. as appropriate (2)
<source lang="sql">
SQL> SQL> SELECT TO_CHAR(SYSDATE, "A.D.")
2 FROM dual;
TO_C
A.D. SQL></source>
AM or PM: AM or PM as appropriate
<source lang="sql">
SQL> SQL> SELECT TO_CHAR(SYSDATE, "AM")
2 FROM dual;
TO -- PM</source>
A.M. or P.M.: A.M. or P.M. as appropriate (2)
<source lang="sql">
SQL> SQL> SELECT TO_CHAR(SYSDATE, "A.M.")
2 FROM dual;
TO_C
P.M. SQL></source>
By default, the database outputs dates in the format DD-MON-YY
By default, the database outputs dates in the format DD-MON-YY, where
YY are the last two digits of the year.
<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> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped.</source>
CC: Two-digit century
<source lang="sql">
SQL> -- SQL> SQL> SELECT TO_CHAR(SYSDATE, "CC")
2 FROM dual;
TO -- 21 SQL></source>
Datetime Formatting Parameters for TO_CHAR() 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
DAY: Full name of the day in uppercase
<source lang="sql">
SQL> SQL> SELECT TO_CHAR(SYSDATE, "DAY")
2 FROM dual;
TO_CHAR(S
WEDNESDAY SQL></source>
Day: Full name of the day with the first letter in uppercase
<source lang="sql">
SQL> SQL> SELECT TO_CHAR(SYSDATE, "Day")
2 FROM dual;
TO_CHAR(S
Wednesday SQL></source>
DAY MON, YY AD
<source lang="sql">
SQL> SQL> SELECT TO_CHAR(SYSDATE, "DAY MON, YY AD")
2 FROM dual;
TO_CHAR(SYSDATE,"DAY
THURSDAY MAY, 07 AD SQL></source>
DDD: Three-digit day of the year
<source lang="sql">
SQL> SQL> SELECT TO_CHAR(SYSDATE, "DDD")
2 FROM dual;
TO_ --- 150 SQL></source>
DDSPTH "of" MONTH, YEAR A.D.
<source lang="sql">
SQL> SQL> SELECT TO_CHAR(SYSDATE, "DDSPTH "of" MONTH, YEAR A.D.")
2 FROM dual;
TO_CHAR(SYSDATE,"DDSPTH"OF"MONTH,YEARA.D.")
THIRTY-FIRST of MAY , TWO THOUSAND SEVEN A.D. SQL></source>
DD:Two-digit day of the month
<source lang="sql">
SQL> SQL> SELECT TO_CHAR(SYSDATE, "DD")
2 FROM dual;
TO -- 30 SQL></source>
D: One-digit day of the week
<source lang="sql">
SQL> SQL> SELECT TO_CHAR(SYSDATE, "D")
2 FROM dual;
T - 4 SQL></source>
DY: First three letters of the name of the day in uppercase
<source lang="sql">
SQL> SQL> SELECT TO_CHAR(SYSDATE, "DY")
2 FROM dual;
TO_ --- WED SQL></source>
Dy: First three letters of the name of the day with the first letter in uppercase
<source lang="sql">
SQL> SQL> SELECT TO_CHAR(SYSDATE, "Dy")
2 FROM dual;
TO_ --- Wed SQL></source>
HH24: Two-digit hour in 24-hour format
<source lang="sql">
SQL> SQL> SELECT TO_CHAR(SYSDATE, "HH24")
2 FROM dual;
TO -- 21 SQL></source>
HH: Two-digit hour in 12-hour format
<source lang="sql">
SQL> SQL> SELECT TO_CHAR(SYSDATE, "HH")
2 FROM dual;
TO -- 09 SQL></source>
I: Last digit of the ISO year
<source lang="sql">
SQL> SQL> SELECT TO_CHAR(SYSDATE, "I")
2 FROM dual;
T - 7 SQL></source>
Insert Date value with default format
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE myTable (
2 name VARCHAR2(25), 3 price NUMBER(4,2), 4 start_date DATE);
Table created. SQL> SQL> SQL> INSERT INTO myTable VALUES (NULL, 2.5, "29-JUN-04"); 1 row created. SQL> SQL> INSERT INTO myTable VALUES ("Product Name 3", null, "10-DEC-05"); 1 row created. SQL> SQL> INSERT INTO myTable VALUES (NULL, NULL, "31-AUG-06"); 1 row created. SQL> SQL> SELECT * FROM myTable; NAME PRICE START_DAT
---------- ---------
2.5 29-JUN-04
Product Name 3 10-DEC-05
31-AUG-06
3 rows selected. SQL> SQL> SQL> drop table myTable; Table dropped. SQL> SQL></source>
IW: Two-digit ISO week of the year
<source lang="sql">
SQL> SQL> SELECT TO_CHAR(SYSDATE, "IW")
2 FROM dual;
TO -- 22 SQL></source>
IY: Last two digits of the ISO year
<source lang="sql">
SQL> SQL> SELECT TO_CHAR(SYSDATE, "IY")
2 FROM dual;
TO -- 07 SQL></source>
IYY: Last three digits of the ISO year
<source lang="sql">
SQL> SQL> SELECT TO_CHAR(SYSDATE, "IYY")
2 FROM dual;
TO_ --- 007 SQL></source>
IYYY: All four digits of the ISO year
<source lang="sql">
SQL> SQL> SELECT TO_CHAR(SYSDATE, "IYYY")
2 FROM dual;
TO_C
2007 SQL></source>
J: Julian day-the number of days that have passed since January 1, 4713 B.C.
<source lang="sql">
SQL> SQL> SELECT TO_CHAR(SYSDATE, "J")
2 FROM dual;
TO_CHAR
2454251 SQL></source>
MI: Two-digit minute
<source lang="sql">
SQL> SQL> SELECT TO_CHAR(SYSDATE, "MI")
2 FROM dual;
TO -- 42 SQL></source>
MM: Two-digit month of the year
<source lang="sql">
SQL> SQL> SELECT TO_CHAR(SYSDATE, "MM")
2 FROM dual;
TO -- 05</source>
MON: First three letters of the name of the month in uppercase
<source lang="sql">
SQL> SQL> SELECT TO_CHAR(SYSDATE, "MON")
2 FROM dual;
TO_ --- MAY</source>
Mon: First three letters of the name of the month with the first letter in uppercase
<source lang="sql">
SQL> SQL> SELECT TO_CHAR(SYSDATE, "Mon")
2 FROM dual;
TO_ --- May</source>
MONTH: Full name of the month in uppercase, right-padded with spaces to a total length of nine characters
<source lang="sql">
SQL> SQL> SELECT TO_CHAR(SYSDATE, "MONTH")
2 FROM dual;
TO_CHAR(S
MAY</source>
Month: Full name of the month with first letter in uppercase, right-padded with spaces to a total length of nine characters
<source lang="sql">
SQL> SQL> SELECT TO_CHAR(SYSDATE, "Month")
2 FROM dual;
TO_CHAR(S
May</source>
Q: One-digit quarter of the year
<source lang="sql">
SQL> SQL> SQL> SELECT TO_CHAR(SYSDATE, "Q")
2 FROM dual;
T - 2</source>
RM:Roman numeral month.
<source lang="sql">
SQL> SQL> SELECT TO_CHAR(SYSDATE, "RM")
2 FROM dual;
TO_C
V</source>
RR: Last two digits of the rounded year, which depends on the current year
<source lang="sql">
SQL> SQL> SELECT TO_CHAR(SYSDATE, "RR")
2 FROM dual;
TO -- 07 SQL></source>
RRRR: All four digits of the rounded year, which depends on the current year
<source lang="sql">
SQL> SQL> SELECT TO_CHAR(SYSDATE, "RRRR")
2 FROM dual;
TO_C
2007 SQL></source>
SCC: Two-digit century with a negative sign (-) for B.C.
<source lang="sql">
SQL> SQL> SQL> SELECT TO_CHAR(SYSDATE, "SCC")
2 FROM dual;
TO_ ---
21</source>
SELECT TO_CHAR(ADD_MONTHS(TO_DATE("01-JAN-15:26","DD-MON-YYYY HH24:MI:SS"), 2), "DD-MON-YYYY HH24:MI:SS") FROM dual;
<source lang="sql">
TO_CHAR(ADD_MONTHS(T
01-MAR-2005 19:15:26</source>
SELECT TO_CHAR(SYSDATE, "DD/MM/YYYY")
<source lang="sql">
2 FROM dual; TO_CHAR(SY
31/05/2007 SQL></source>
SELECT TO_CHAR(SYSDATE, "PM")
<source lang="sql">
2 FROM dual; TO -- PM SQL></source>
SELECT TO_CHAR(SYSDATE, "P.M.") (2)
<source lang="sql">
2 FROM dual; TO_C
P.M. SQL></source>
SELECT TO_CHAR(TO_DATE("05-FEB-1968"), "MONTH DD, YYYY")
<source lang="sql">
SQL> SELECT TO_CHAR(TO_DATE("05-FEB-1968"), "MONTH DD, YYYY")
2 FROM dual;
TO_CHAR(TO_DATE("0
FEBRUARY 05, 1968 SQL></source>
SS: Two-digit second
<source lang="sql">
SQL> SQL> SELECT TO_CHAR(SYSDATE, "SS")
2 FROM dual;
TO -- 40 SQL></source>
SYYYY: All four digits of the year with a negative sign (-) for B.C.
<source lang="sql">
SQL> SQL> SELECT TO_CHAR(SYSDATE, "SYYYY")
2 FROM dual;
TO_CH
2007
SQL></source>
-/,.;: "text"
Characters that allow you to separate the aspects of a date and time.
You can supply freeform text in quotes as a separator.
<source lang="sql">
SQL> SQL> SELECT TO_CHAR(SYSDATE, "DD-MM-YYYY")
2 FROM dual;
TO_CHAR(SY
31-05-2007</source>
TO_CHAR(SYSDATE, "BC")
<source lang="sql">
SQL> SELECT TO_CHAR(SYSDATE, "BC")
2 FROM dual;
TO -- AD SQL></source>
TO_CHAR(SYSDATE, "B.C.") (2)
<source lang="sql">
SQL> SELECT TO_CHAR(SYSDATE, "B.C.")
2 FROM dual;
TO_C
A.D. SQL></source>
W: One-digit week of the month
<source lang="sql">
SQL> SQL> SELECT TO_CHAR(SYSDATE, "W")
2 FROM dual;
T - 5 SQL></source>
WW: Two-digit week of the year
<source lang="sql">
SQL> SQL> SELECT TO_CHAR(SYSDATE, "WW")
2 FROM dual;
TO -- 22 SQL></source>
YEAR: Name of the year in uppercase
<source lang="sql">
SQL> SQL> SELECT TO_CHAR(SYSDATE, "YEAR")
2 FROM dual;
TO_CHAR(SYSDATE,"YEAR")
TWO THOUSAND SEVEN SQL></source>
Year: Name of the year with the first letter in uppercase
<source lang="sql">
SQL> SELECT TO_CHAR(SYSDATE, "Year")
2 FROM dual;
TO_CHAR(SYSDATE,"YEAR")
Two Thousand Seven SQL></source>
Y: Last digit of the year
<source lang="sql">
SQL> SELECT TO_CHAR(SYSDATE, "Y")
2 FROM dual;
T - 7 SQL></source>
YY: Last two digits of the year
<source lang="sql">
SQL> SQL> SELECT TO_CHAR(SYSDATE, "YY")
2 FROM dual;
TO -- 07 SQL></source>
YYY: Last three digits of the year
<source lang="sql">
SQL> SQL> SELECT TO_CHAR(SYSDATE, "YYY")
2 FROM dual;
TO_ --- 007 SQL></source>
YYYY: All four digits of the year
<source lang="sql">
SQL> SQL> SELECT TO_CHAR(SYSDATE, "YYYY")
2 FROM dual;
TO_C
2007 SQL></source>
Y,YYY: All four digits of the year with a comma
<source lang="sql">
SQL> SQL> SELECT TO_CHAR(SYSDATE, "Y,YYY")
2 FROM dual;
TO_CH
2,007 SQL></source>
