Date/time functions and operators v16

The date/time functions table shows the available functions for date/time value processing.

Overview of basic arithmetic operators

The following table shows the behaviors of the basic arithmetic operators (+, -). For formatting functions, refer to IMMUTABLE TO_CHAR(TIMESTAMP, format). For more information on date/time data types, see Date/time types.

OperatorExampleResult
plus (+)DATE '2001-09-28' + 705-OCT-01 00:00:00
plus (+)TIMESTAMP '2001-09-28 13:30:00' + 301-OCT-01 13:30:00
minus (-)DATE '2001-10-01' – 724-SEP-01 00:00:00
minus (-)TIMESTAMP '2001-09-28 13:30:00' - 325-SEP-01 13:30:00
minus (-)TIMESTAMP '2001-09-29 03:00:00' - TIMESTAMP '2001-09-27 12:00:00'@ 1 day 15 hours

Overview of date and time functions

In the date/time functions of the following table the use of the DATE and TIMESTAMP data types are interchangeable.

FunctionReturn typeDescriptionExampleResult
ADD_MONTHS (DATE, NUMBER)DATEAdds months to a date.ADD_MONTHS ('28-FEB-97', ,3.8)31-MAY-97 00:00:00
CURRENT_DATEDATECurrent date.CURRENT_DATE04-JUL-07
CURRENT_TIMESTAMPTIMESTAMPReturns the current date and time.CURRENT_TIMESTAMP04-JUL-07 15:33:23.484
EXTRACT(field FROM TIMESTAMP)DOUBLE PRECISIONGets subfield.EXTRACT(hour FROM TIMESTAMP '2001-02-16 20:38:40')20
LAST_DAY(DATE)DATEReturns the last day of the month represented by the given date. If the given date contains a time portion, it's carried forward to the result unchanged.LAST_DAY('14-APR-98')30-APR-98 00:00:00
LOCALTIMESTAMP [ (precision) ]TIMESTAMPCurrent date and time (start of current transaction).LOCALTIMESTAMP04-JUL-07 15:33:23.484
MONTHS_BETWEEN (DATE, DATE)NUMBERNumber of months between two dates.MONTHS_BETWEEN ('28-FEB-07', '30-NOV-06')3
NEXT_DAY(DATE, dayofweek)DATEDate falling on dayofweek following specified date.NEXT_DAY('16-APR-07',FRI' )20-APR-07 00:00:00
NEW_TIME(DATE, VARCHAR, VARCHAR)DATEConverts a date and time to an alternate time zone.NEW_TIME (TO_DATE '2005/05/29 01:45', 'AST', 'PST')2005/05/29 21:45:00
NUMTODSINTERVAL(NUMBER, INTERVAL)INTERVALConverts a number to a specified day or second interval.SELECT numtodsinterval(100, ‘hour’);4 days 04:00:00
NUMTOYMINTERVAL(NUMBER, INTERVAL)INTERVALConverts a number to a specified year or month interval.SELECT numtoyminterval(100, ‘month’);8 years 4 mons
ROUND(DATE [, format ])DATEDate rounded according to format.ROUND(TO_DATE('29-MAY-05'),'MON')01-JUN-05 00:00:00
SYS_EXTRACT_UTC(TIMESTAMP WITH TIME ZONE)TIMESTAMPReturns Coordinated Universal Time.SYS_EXTRACT_UTC(CAST ('24-MAR-11 12:30:00PM -04:00' AS TIMESTAMP WITH TIME ZONE)) 24-MAR-11 16:30:00
SYSDATEDATEReturns current date and time.SYSDATE01-AUG-12 11:12:34
SYSTIMESTAMP()TIMESTAMPReturns current date and time.SYSTIMESTAMP01-AUG-12 11:11:23. 665229 -07:00
TRUNC(DATE [format])DATETruncates according to format.TRUNC(TO_DATE ('29-MAY-05'), 'MON')01-MAY-05 00:00:00
DBTIMEZONE[+|-]TZH:TZM or time zone region nameReturns time zone offset in the format [+|-]TZH:TZM or time zone region name.DBTIMEZONE-9:00 or Europe/London
SESSIONTIMEZONE[+|-]TZH:TZM or time zone region nameReturns time zone offset in the format [+|-]TZH:TZM or time zone region name.SESSIONTIMEZONE-9:00 or Europe/London

ADD_MONTHS

The ADD_MONTHS functions adds or subtracts the specified number of months to or from the given date. Use a negative value to subtract. The resulting day of the month is the same as the day of the month of the given date except when the day is the last day of the month. In that case, the resulting date always falls on the last day of the month.

Any fractional portion of the number of months parameter is truncated before performing the calculation.

If the given date contains a time portion, it's carried forward to the result unchanged.

These examples show the ADD_MONTHS function:

SELECT ADD_MONTHS('13-JUN-07',4) FROM DUAL;
Output
     add_months
--------------------
 13-OCT-07 00:00:00
(1 row)
SELECT ADD_MONTHS('31-DEC-06',2) FROM DUAL;
Output
     add_months
--------------------
 28-FEB-07 00:00:00
(1 row)
SELECT ADD_MONTHS('31-MAY-04',-3) FROM DUAL;
Output
     add_months
--------------------
 29-FEB-04 00:00:00
(1 row)

CURRENT DATE/TIME

EDB Postgres Advanced Server provides functions that return values related to the current date and time. These functions return values based on the start time of the current transaction:

  • CURRENT_DATE
  • CURRENT_TIMESTAMP
  • LOCALTIMESTAMP
  • LOCALTIMESTAMP(precision)

CURRENT_DATE returns the current date and time based on the start time of the current transaction. The value of CURRENT_DATE doesn't change if called multiple times in a transaction.

SELECT CURRENT_DATE FROM DUAL;
Output
date
-----------
 06-AUG-07

CURRENT_TIMESTAMP returns the current date and time. When called from a single SQL statement, it returns the same value for each occurrence in the statement. If called from multiple statements in a transaction, it might return different values for each occurrence. If called from a function, it might return a value different from the one returned by current_timestamp in the caller.

SELECT CURRENT_TIMESTAMP, CURRENT_TIMESTAMP FROM DUAL;
Output
current_timestamp | current_timestamp
----------------------------------+----------------------------------
 02-SEP-13 17:52:29.261473 +05:00 | 02-SEP-13 17:52:29.261474 +05:00

You can optionally give LOCALTIMESTAMP a precision parameter. The parameter causes the result to be rounded to the specified number of fractional digits in the seconds field. Without a precision parameter, the result is given to the full available precision.

SELECT LOCALTIMESTAMP FROM DUAL;
Output
timestamp
------------------------
 06-AUG-07 16:11:35.973
(1 row)
SELECT LOCALTIMESTAMP(2) FROM DUAL;
Output
timestamp
-----------------------
 06-AUG-07 16:11:44.58
(1 row)

Since these functions return the start time of the current transaction, their values don't change during the transaction. The intent is to allow a single transaction to have a consistent notion of the “current” time, so that multiple modifications in the same transaction have the same timestamp. Other database systems might advance these values more frequently.

TIMEZONE functions

EDB Postgres Advanced Server provides functions that return timezone set values.

DBTIMEZONE

DBTIMEZONE returns the value of the database time zone. The return type is a time zone offset in the format [+\|-]TZH:TZM or a time zone region name. The return type depends on the database time zone value specified in the recent CREATE DATABASE or ALTER DATABASE statement.

ALTER DATABASE edb SET TIMEZONE='-09:00';
SELECT dbtimezone FROM DUAL;
Output
 dbtimezone 
------------
 -09:00
(1 row)
ALTER DATABASE edb SET TIMEZONE='Europe/London';
SELECT dbtimezone FROM DUAL;
Output
  dbtimezone   
---------------
 Europe/London
(1 row)

SESSIONTIMEZONE

SESSIONTIMEZONE returns the time zone of the current session. The return type is a time zone offset in the format [+\|-]TZH:TZM or a time zone region name. The return type depends on the session time zone value specified in the recent ALTER SESSION or SET statement.

ALTER SESSION SET timezone to 'America/New_York';
SELECT sessiontimezone() FROM dual;
Output
 sessiontimezone  
------------------
 America/New_York
(1 row)
SET timezone TO '+5:30';
SELECT sessiontimezone() FROM dual;
Output
 sessiontimezone 
-----------------
 +5:30
(1 row)

EXTRACT

The EXTRACT function retrieves subfields such as year or hour from date/time values. The EXTRACT function returns values of type DOUBLE PRECISION. The following are valid field names:

YEAR

The year field.

SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;
Output
 date_part
-----------
     2001
(1 row)

MONTH

The number of the month in the year (1 - 12).

SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;
Output
 date_part
-----------
       2
(1 row)

DAY

The day of the month field (1 - 31).

SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;
Output
 date_part
-----------
       16
(1 row)

HOUR

The hour field (0 - 23).

SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;
Output
 date_part
-----------
       20
(1 row)

MINUTE

The minutes field (0 - 59).

SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;
Output
 date_part
-----------
       38
(1 row)

SECOND

The seconds field, including fractional parts (0 - 59).

SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;
Output
 date_part
-----------
       40
(1 row)

MONTHS_BETWEEN

The MONTHS_BETWEEN function returns the number of months between two dates. The result is a numeric value that's positive if the first date is greater than the second date. It's negative if the first date is less than the second date.

The result is always a whole number of months if the day of the month of both date parameters is the same or both date parameters fall on the last day of their respective months.

These examples show the MONTHS_BETWEEN function:

SELECT MONTHS_BETWEEN('15-DEC-06','15-OCT-06') FROM DUAL;
Output
 months_between
----------------
              2
(1 row)
SELECT MONTHS_BETWEEN('15-OCT-06','15-DEC-06') FROM DUAL;
Output
 months_between
----------------
             -2
(1 row)
SELECT MONTHS_BETWEEN('31-JUL-00','01-JUL-00') FROM DUAL;
Output
 months_between
----------------
    0.967741935
(1 row)
SELECT MONTHS_BETWEEN('01-JAN-07','01-JAN-06') FROM DUAL;
Output
 months_between
----------------
             12
(1 row)

NEXT_DAY

The NEXT_DAY function returns the first occurrence of the given weekday strictly greater than the given date. You must specify at least the first three letters of the weekday, for example, SAT. If the given date contains a time portion, it's carried forward to the result unchanged.

These examples show the NEXT_DAY function.

SELECT NEXT_DAY(TO_DATE('13-AUG-07','DD-MON-YY'),'SUNDAY') FROM DUAL;
Output
      next_day
--------------------
 19-AUG-07 00:00:00
(1 row)
SELECT NEXT_DAY(TO_DATE('13-AUG-07','DD-MON-YY'),'MON') FROM DUAL;
Output
      next_day
--------------------
 20-AUG-07 00:00:00
(1 row)

NEW_TIME

The NEW_TIME function converts a date and time from one time zone to another. NEW_TIME returns a value of type DATE. The syntax is:

NEW_TIME(DATE, time_zone1, time_zone2)

time_zone1 and time_zone2 must be string values from the Time zone column of the following table.

Time zoneOffset from UTCDescription
ASTUTC+4Atlantic Standard Time
ADTUTC+3Atlantic Daylight Time
BSTUTC+11Bering Standard Time
BDTUTC+10Bering Daylight Time
CSTUTC+6Central Standard Time
CDTUTC+5Central Daylight Time
ESTUTC+5Eastern Standard Time
EDTUTC+4Eastern Daylight Time
GMTUTCGreenwich Mean Time
HSTUTC+10Alaska-Hawaii Standard Time
HDTUTC+9Alaska-Hawaii Daylight Time
MSTUTC+7Mountain Standard Time
MDTUTC+6Mountain Daylight Time
NSTUTC+3:30Newfoundland Standard Time
PSTUTC+8Pacific Standard Time
PDTUTC+7Pacific Daylight Time
YSTUTC+9Yukon Standard Time
YDTUTC+8Yukon Daylight Time

This example shows the NEW_TIME function:

SELECT NEW_TIME(TO_DATE('08-13-07 10:35:15','MM-DD-YY HH24:MI:SS'),'AST',
'PST') "Pacific Standard Time" FROM DUAL;
Output
Pacific Standard Time
---------------------
 13-AUG-07 06:35:15
(1 row)

NUMTODSINTERVAL

The NUMTODSINTERVAL function converts a numeric value to a time interval that includes day-through-second interval units. When calling the function, specify the smallest fractional interval type to include in the result set. The valid interval types are DAY, HOUR, MINUTE, and SECOND.

This example converts a numeric value to a time interval that includes days and hours:

SELECT numtodsinterval(100,hour);
Output
numtodsinterval
---------------
4 days 04:00:00
(1 row)

This example converts a numeric value to a time interval that includes minutes and seconds:

SELECT numtodsinterval(100,second);
Output
numtodsinterval
---------------
1 min 40 secs
(1 row)

NUMTOYMINTERVAL

The NUMTOYMINTERVAL function converts a numeric value to a time interval that includes year-through-month interval units. When calling the function, specify the smallest fractional interval type to include in the result set. The valid interval types are YEAR and MONTH.

This example converts a numeric value to a time interval that includes years and months:

SELECT numtoyminterval(100,month);
Output
numtoyminterval
---------------
8 years 4 mons
(1 row)

This example converts a numeric value to a time interval that includes years only:

SELECT numtoyminterval(100,year);
Output
numtoyminterval
---------------
100 years
(1 row)

ROUND

The ROUND function returns a date rounded according to a specified template pattern. If you omit the template pattern, the date is rounded to the nearest day. The following table shows the template patterns for the ROUND function.

PatternDescription
CC, SCCReturns January 1, cc01 where cc is first 2 digits of the given year if last 2 digits <= 50, or 1 greater than the first 2 digits of the given year if last 2 digits > 50; (for AD years)
SYYY, YYYY, YEAR, SYEAR, YYY, YY, YReturns January 1, yyyy where yyyy is rounded to the nearest year; rounds down on June 30, rounds up on July 1
IYYY, IYY, IY, IRounds to the beginning of the ISO year, which is determined by rounding down if the month and day is on or before June 30th or by rounding up if the month and day is July 1st or later
QReturns the first day of the quarter determined by rounding down if the month and day is on or before the 15th of the second month of the quarter or by rounding up if the month and day is on the 16th of the second month or later of the quarter
MONTH, MON, MM, RMReturns the first day of the specified month if the day of the month is on or prior to the 15th; returns the first day of the following month if the day of the month is on the 16th or later
WWRounds to the nearest date that corresponds to the same day of the week as the first day of the year
IWRounds to the nearest date that corresponds to the same day of the week as the first day of the ISO year
WRounds to the nearest date that corresponds to the same day of the week as the first day of the month
DDD, DD, JRounds to the start of the nearest day; 11:59:59 AM or earlier rounds to the start of the same day; 12:00:00 PM or later rounds to the start of the next day
DAY, DY, DRounds to the nearest Sunday
HH, HH12, HH24Round to the nearest hour
MIRounds to the nearest minute

These examples show the use of the ROUND function.

These examples round to the nearest hundred years:

SELECT TO_CHAR(ROUND(TO_DATE('1950','YYYY'),'CC'),'DD-MON-YYYY') "Century" FROM DUAL;
Output
   Century
-------------
 01-JAN-1901
(1 row)
SELECT TO_CHAR(ROUND(TO_DATE('1951','YYYY'),'CC'),'DD-MON-YYYY') "Century" FROM DUAL;
Output
   Century
-------------
 01-JAN-2001
(1 row)

These examples round to the nearest year:

SELECT TO_CHAR(ROUND(TO_DATE('30-JUN-1999','DD-MON-YYYY'),'Y'),'DD-MON-YYYY') "Year" FROM DUAL;
Output
Year
-------------
 01-JAN-1999
(1 row)
SELECT TO_CHAR(ROUND(TO_DATE('01-JUL-1999','DD-MON-YYYY'),'Y'),'DD-MON-YYYY') "Year" FROM DUAL;
Output
Year
-------------
 01-JAN-2000
(1 row)

These examples round to the nearest ISO year. The first example rounds to 2004, and the ISO year for 2004 begins on December 29, 2003. The second example rounds to 2005, and the ISO year for 2005 begins on January 3 of that same year.

(An ISO year begins on the first Monday from which a seven-day span, Monday through Sunday, contains at least four days of the new year. It's possible for the beginning of an ISO year to start in December of the prior year.)

SELECT TO_CHAR(ROUND(TO_DATE('30-JUN-2004','DD-MON-YYYY'),'IYYY'),'DD-MON-YYYY') "ISO Year" FROM DUAL;
Output
  ISO Year
-------------
 29-DEC-2003
(1 row)
SELECT TO_CHAR(ROUND(TO_DATE('01-JUL-2004','DD-MON-YYYY'),'IYYY'),'DD-MON-YYYY') "ISO Year" FROM DUAL;
Output
  ISO Year
-------------
 03-JAN-2005
(1 row)

These examples round to the nearest quarter:

SELECT ROUND(TO_DATE('15-FEB-07','DD-MON-YY'),'Q') "Quarter" FROM DUAL;
Output
      Quarter
--------------------
 01-JAN-07 00:00:00
(1 row)
SELECT ROUND(TO_DATE('16-FEB-07','DD-MON-YY'),'Q') "Quarter" FROM DUAL;
Output
      Quarter
--------------------
 01-APR-07 00:00:00
(1 row)

These examples round to the nearest month:

SELECT ROUND(TO_DATE('15-DEC-07','DD-MON-YY'),'MONTH') "Month" FROM DUAL;
Output
Month
--------------------
 01-DEC-07 00:00:00
(1 row)
SELECT ROUND(TO_DATE('16-DEC-07','DD-MON-YY'),'MONTH') "Month" FROM DUAL;
Output
Month
--------------------
 01-JAN-08 00:00:00
(1 row)

These examples round to the nearest week. The first day of 2007 lands on a Monday. In the first example, January 18 is closest to the Monday that lands on January 15. In the second example, January 19 is closer to the Monday that falls on January 22.

SELECT ROUND(TO_DATE('18-JAN-07','DD-MON-YY'),'WW') "Week" FROM DUAL;
Output
        Week
--------------------
 15-JAN-07 00:00:00
(1 row)
SELECT ROUND(TO_DATE('19-JAN-07','DD-MON-YY'),'WW') "Week" FROM DUAL;
Output
        Week
--------------------
 22-JAN-07 00:00:00
(1 row)

These examples round to the nearest ISO week. An ISO week begins on a Monday. In the first example, January 1, 2004 is closest to the Monday that lands on December 29, 2003. In the second example, January 2, 2004 is closer to the Monday that lands on January 5, 2004.

SELECT ROUND(TO_DATE('01-JAN-04','DD-MON-YY'),'IW') "ISO Week" FROM DUAL;
Output
      ISO Week
--------------------
 29-DEC-03 00:00:00
(1 row)
SELECT ROUND(TO_DATE('02-JAN-04','DD-MON-YY'),'IW') "ISO Week" FROM DUAL;
Output
      ISO Week
--------------------
 05-JAN-04 00:00:00
(1 row)

These examples round to the nearest week, where a week is considered to start on the same day as the first day of the month:

SELECT ROUND(TO_DATE('05-MAR-07','DD-MON-YY'),'W') "Week" FROM DUAL;
Output
        Week
--------------------
 08-MAR-07 00:00:00
(1 row)
SELECT ROUND(TO_DATE('04-MAR-07','DD-MON-YY'),'W') "Week" FROM DUAL;
Output
        Week
--------------------
 01-MAR-07 00:00:00
(1 row)

These examples round to the nearest day:

SELECT ROUND(TO_DATE('04-AUG-07 11:59:59 AM','DD-MON-YY HH:MI:SS AM'),'J')
"Day" FROM DUAL;
Output
Day
--------------------
 04-AUG-07 00:00:00
(1 row)
SELECT ROUND(TO_DATE('04-AUG-07 12:00:00 PM','DD-MON-YY HH:MI:SS AM'),'J')
"Day" FROM DUAL;
Output
Day
--------------------
 05-AUG-07 00:00:00
(1 row)

These examples round to the start of the nearest day of the week (Sunday):

SELECT ROUND(TO_DATE('08-AUG-07','DD-MON-YY'),'DAY') "Day of Week" FROM DUAL;
Output
Day of Week
--------------------
 05-AUG-07 00:00:00
(1 row)
SELECT ROUND(TO_DATE('09-AUG-07','DD-MON-YY'),'DAY') "Day of Week" FROM DUAL;
Output
Day of Week
--------------------
 12-AUG-07 00:00:00
(1 row)

These examples round to the nearest hour:

SELECT TO_CHAR(ROUND(TO_DATE('09-AUG-07 08:29','DD-MON-YY HH:MI'),'HH'),'DD-MON-YY HH24:MI:SS') "Hour" FROM DUAL;
Output
Hour
--------------------
 09-AUG-07 08:00:00
(1 row)
SELECT TO_CHAR(ROUND(TO_DATE('09-AUG-07 08:30','DD-MON-YY HH:MI'),'HH'),'DD-MON-YY HH24:MI:SS') "Hour" FROM DUAL;
Output
Hour
--------------------
 09-AUG-07 09:00:00
(1 row)

These examples round to the nearest minute:

SELECT TO_CHAR(ROUND(TO_DATE('09-AUG-07 08:30:29','DD-MON-YY
HH:MI:SS'),'MI'),'DD-MON-YY HH24:MI:SS') "Minute" FROM DUAL;
Output
Minute
--------------------
 09-AUG-07 08:30:00
(1 row)
SELECT TO_CHAR(ROUND(TO_DATE('09-AUG-07 08:30:30','DD-MON-YY
HH:MI:SS'),'MI'),'DD-MON-YY HH24:MI:SS') "Minute" FROM DUAL;
Output
Minute
--------------------
 09-AUG-07 08:31:00
(1 row)

SYSDATE

The SYSDATE function returns the current date and time (timestamp without timezone) of the operating system on which the database server resides. The function is STABLE and requires no arguments.

When called from a single SQL statement, it returns the same value for each occurrence in the statement. If called from multiple statements in a transaction, it might return different values for each occurrence. If called from a function, it might return a value different from the one returned by SYSDATE in the caller.

This example shows a call to SYSDATE:

SELECT SYSDATE, SYSDATE FROM DUAL;
Output
   sysdate          |      sysdate
--------------------+--------------------
 28-APR-20 16:45:28 | 28-APR-20 16:45:28
(1 row)

TRUNC

The TRUNC function returns a date truncated according to a specified template pattern. If you omit the template pattern, the date is truncated to the nearest day. The following table shows the template patterns for the TRUNC function.

PatternDescription
CC, SCCReturns January 1, cc 01 where cc is first 2 digits of the given year
SYYY, YYYY, YEAR, SYEAR, YYY, YY, YReturns January 1, yyyy where yyyy is the given year
IYYY, IYY, IY, IReturns the start date of the ISO year containing the given date
QReturns the first day of the quarter containing the given date
MONTH, MON, MM, RMReturns the first day of the specified month
WWReturns the largest date just prior to or the same as the given date that corresponds to the same day of the week as the first day of the year
IWReturns the start of the ISO week containing the given date
WReturns the largest date just prior to, or the same as the given date that corresponds to the same day of the week as the first day of the month
DDD, DD, JReturns the start of the day for the given date
DAY, DY, DReturns the start of the week (Sunday) containing the given date
HH, HH12, HH24Returns the start of the hour
MIReturns the start of the minute

Following are examples that use the TRUNC function.

This example truncates down to the hundred-years unit:

SELECT TO_CHAR(TRUNC(TO_DATE('1951','YYYY'),'CC'),'DD-MON-YYYY') "Century" FROM DUAL;
Output
   Century
-------------
 01-JAN-1901
(1 row)

This example truncates down to the year:

SELECT TO_CHAR(TRUNC(TO_DATE('01-JUL-1999','DD-MON-YYYY'),'Y'),'DD-MON-YYYY') "Year" FROM DUAL;
Output
Year
-------------
 01-JAN-1999
(1 row)

This example truncates down to the beginning of the ISO year:

SELECT TO_CHAR(TRUNC(TO_DATE('01-JUL-2004','DD-MON-YYYY'),'IYYY'),'DD-MON-YYYY') "ISO Year" FROM DUAL;
Output
  ISO Year
-------------
 29-DEC-2003
(1 row)

This example truncates down to the start date of the quarter:

SELECT TRUNC(TO_DATE('16-FEB-07','DD-MON-YY'),'Q') "Quarter" FROM DUAL;
Output
      Quarter
--------------------
 01-JAN-07 00:00:00
(1 row)

This example truncates to the start of the month:

SELECT TRUNC(TO_DATE('16-DEC-07','DD-MON-YY'),'MONTH') "Month" FROM DUAL;
Output
Month
--------------------
 01-DEC-07 00:00:00
(1 row)

This example truncates down to the start of the week determined by the first day of the year. The first day of 2007 lands on a Monday, so the Monday just prior to January 19 is January 15.

SELECT TRUNC(TO_DATE('19-JAN-07','DD-MON-YY'),'WW') "Week" FROM DUAL;
Output
        Week
--------------------
 15-JAN-07 00:00:00
(1 row)

This example truncates to the start of an ISO week. An ISO week begins on a Monday. January 2, 2004 falls in the ISO week that starts on Monday, December 29, 2003.

SELECT TRUNC(TO_DATE('02-JAN-04','DD-MON-YY'),'IW') "ISO Week" FROM DUAL;
Output
      ISO Week
--------------------
 29-DEC-03 00:00:00
(1 row)

This example truncates to the start of the week, where a week is considered to start on the same day as the first day of the month:

SELECT TRUNC(TO_DATE('21-MAR-07','DD-MON-YY'),'W') "Week" FROM DUAL;
Output
        Week
--------------------
 15-MAR-07 00:00:00
(1 row)

This example truncates to the start of the day:

SELECT TRUNC(TO_DATE('04-AUG-07 12:00:00 PM','DD-MON-YY HH:MI:SS AM'),'J')
"Day" FROM DUAL;
Output
Day
--------------------
 04-AUG-07 00:00:00
(1 row)

This example truncates to the start of the week (Sunday):

SELECT TRUNC(TO_DATE('09-AUG-07','DD-MON-YY'),'DAY') "Day of Week" FROM DUAL;
Output
Day of Week
--------------------
 05-AUG-07 00:00:00
(1 row)

This example truncates to the start of the hour:

SELECT TO_CHAR(TRUNC(TO_DATE('09-AUG-07 08:30','DD-MON-YY HH:MI'),'HH'),'DD-MON-YY HH24:MI:SS') "Hour" FROM DUAL;
Output
Hour
--------------------
 09-AUG-07 08:00:00
(1 row)

This example truncates to the minute:

SELECT TO_CHAR(TRUNC(TO_DATE('09-AUG-07 08:30:30','DD-MON-YY
HH:MI:SS'),'MI'),'DD-MON-YY HH24:MI:SS') "Minute" FROM DUAL;
Output
Minute
--------------------
 09-AUG-07 08:30:00
(1 row)