Oracle PL/SQL/Date Timezone/TRUNC Date
Содержание
- 1 formats used with the TRUNC() function for the DATE data type
- 2 Trunc a date
- 3 truncate and round a date that falls into the second part of a year, the results would be different:
- 4 Truncates 7:45:26 P.M. on May, to the hour
- 5 Truncates May, to the first day in the month
- 6 TRUNC: Removing the time from SYSDATE
- 7 TRUNC(SYSDATE,"Q"): Truncating to the quarter
- 8 TRUNC(): truncate May, to the first day in the year
- 9 Use TRUNC(Date value) in where clause
- 10 Use TRUNC(date_variable) when you want to extract a date from a timestamp
formats used with the TRUNC() function for the DATE data type
Format Element Description Example
CC One greater than the first two digits of a four-digit year. ROUND(SYSDATE,"CC")
SCC One greater than the first two digits of a four-digit year. ROUND(SYSDATE,"SCC")
Year
SYYYY Year; rounds up on July 1. ROUND(SYSDATE,"SYYYY")
YYYY Year; rounds up on July 1. ROUND(SYSDATE,"YYYY")
YEAR Year; rounds up on July 1. ROUND(SYSDATE,"YEAR")
YYY Year; rounds up on July 1. ROUND(SYSDATE,"YYY")
YY Year; rounds up on July 1. ROUND(SYSDATE,"YY")
Y Year; rounds up on July 1. ROUND(SYSDATE,"Y")
IYYY ISO year. ROUND(SYSDATE,"IYYY")
IY ISO year. ROUND(SYSDATE,"IY")
I ISO year. ROUND(SYSDATE,"I")
Q Quarter; rounds up on sixteenth day of the second month of the quarter. ROUND(SYSDATE,"Q")
Month
MONTH Month; rounds up on sixteenth day. TRUNC(SYSDATE,"MONTH")
MON Month; rounds up on sixteenth day. TRUNC(SYSDATE,"MON")
MM Month; rounds up on sixteenth day. TRUNC(SYSDATE,"MM")
RM Month; rounds up on sixteenth day. TRUNC(SYSDATE,"RM")
Week
WW Same day of the week as the first day of the year. TRUNC(SYSDATE,"WW")
IW Same day of the week as the first day of the ISO year. TRUNC(SYSDATE,"IW")
W Same day of the week as the first day of the month. TRUNC(SYSDATE,"W")
Day
DD Day of the month (from 1 to 31). ROUND(SYSDATE,"DD")
DDD Day of the year (from 1 to 366). ROUND(SYSDATE,"DDD")
J Day. ROUND(SYSDATE,"J")
Starting Day of the week.
DY Starting Day of the week. TRUNC(SYSDATE,"DY")
DAY Starting Day of the week. TRUNC (SYSDATE,"DAY")
HH Hour of the day (from 1 to 12). TRUNC (SYSDATE,"HH")
HH12 Hour of the day (from 1 to 12). TRUNC (SYSDATE,"HH12")
HH24 Hour of the day (from 0 to 23). TRUNC (SYSDATE,"HH24")
MI Minute (from 0 to 59). TRUNC (SYSDATE,"MI")
--
Trunc a date
SQL>
SQL> select trunc( sysdate )
2 from dual
3 /
TRUNC(SYSDATE)
--------------------
16-JUN-2008 00:00:00
1 row selected.
SQL> --
truncate and round a date that falls into the second part of a year, the results would be different:
SQL>
SQL> SELECT
2 SYSDATE + 100 fall_date,
3 TRUNC(SYSDATE + 100, "YEAR") truncated,
4 ROUND(SYSDATE + 100, "YEAR") rounded
5 FROM DUAL;
FALL_DATE TRUNCATED ROUNDED
--------- --------- ---------
28-SEP-08 01-JAN-08 01-JAN-09
SQL>
Truncates 7:45:26 P.M. on May, to the hour
SQL>
SQL> --Truncates 7:45:26 P.M. on May 25, 2005, to the hour
SQL>
SQL> SELECT TO_CHAR(TRUNC(TO_DATE("25-MAY-2005 19:45:26","DD-MON-YYYY HH24:MI:SS"), "HH24"), "DD-MON-YYYY HH24:MI:SS") FROM dual;
TO_CHAR(TRUNC(TO_DAT
--------------------
25-MAY-2005 19:00:00
SQL>
Truncates May, to the first day in the month
SQL>
SQL> SELECT TRUNC(TO_DATE("25-MAY-2005"), "MM") FROM dual;
TRUNC(TO_
---------
01-MAY-05
SQL>
TRUNC: Removing the time from SYSDATE
SQL>
SQL> -- Removing the time from SYSDATE.
SQL>
SQL> SELECT TO_CHAR(TRUNC(SYSDATE),"MM/DD/YYYY HH:MM:SS AM") "Today"s Date and Time" from DUAL;
Today"s Date and Time
----------------------
08/31/2006 12:08:00 AM
SQL>
SQL>
TRUNC(SYSDATE,"Q"): Truncating to the quarter
SQL> -- Truncating to the quarter.
SQL>
SQL> SELECT TO_CHAR(TRUNC(SYSDATE,"Q"),"MM/DD/YYYY HH:MM:SS AM") "Today"s Date and Time" from DUAL;
Today"s Date and Time
----------------------
07/01/2006 12:07:00 AM
SQL>
SQL>
SQL>
TRUNC(): truncate May, to the first day in the year
SQL>
SQL> SELECT TRUNC(TO_DATE("25-MAY-2005"), "YYYY") FROM dual;
TRUNC(TO_
---------
01-JAN-05
SQL>
SQL>
SQL>
Use TRUNC(Date value) in where clause
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>
SQL> SELECT *
2 FROM emp
3 WHERE TRUNC(hiredate) = "08-MAR-90";
no rows selected
SQL>
SQL>
SQL> drop table emp;
Table dropped.
Use TRUNC(date_variable) when you want to extract a date from a timestamp
SQL>
SQL> DECLARE
2 d DATE := SYSDATE;
3 BEGIN
4 dbms_output.put_line(TO_CHAR(TRUNC(d),"DD-MON-YY HH24:MI:SS"));
5 END;
6 /
26-OCT-09 00:00:00
PL/SQL procedure successfully completed.
SQL>