Oracle PL/SQL/Date Timezone/TRUNC Date

formats used with the TRUNC() function for the DATE data type

   <source lang="sql">
  

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") --

 </source>
   
  

Trunc a date

   <source lang="sql">
  

SQL> SQL> select trunc( sysdate )

 2  from dual
 3  /

TRUNC(SYSDATE)

---

16-JUN-2008 00:00:00 1 row selected. SQL> --

 </source>
   
  

truncate and round a date that falls into the second part of a year, the results would be different:

   <source lang="sql">
  

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>

 </source>
   
  

Truncates 7:45:26 P.M. on May, to the hour

   <source lang="sql">
 

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>

 </source>
   
  

Truncates May, to the first day in the month

   <source lang="sql">
   

SQL> SQL> SELECT TRUNC(TO_DATE("25-MAY-2005"), "MM") FROM dual; TRUNC(TO_

---

01-MAY-05 SQL>

 </source>
   
  

TRUNC: Removing the time from SYSDATE

   <source lang="sql">
 

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>

 </source>
   
  

TRUNC(SYSDATE,"Q"): Truncating to the quarter

   <source lang="sql">
 

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>

 </source>
   
  

TRUNC(): truncate May, to the first day in the year

   <source lang="sql">
   

SQL> SQL> SELECT TRUNC(TO_DATE("25-MAY-2005"), "YYYY") FROM dual; TRUNC(TO_

---

01-JAN-05 SQL> SQL> SQL>

 </source>
   
  

Use TRUNC(Date value) in where clause

   <source lang="sql">
  

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.

 </source>
   
  

Use TRUNC(date_variable) when you want to extract a date from a timestamp

   <source lang="sql">
   

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>

 </source>