Date and Time TRUNC

Truncates a DATE or DATETIME value to a specified resolution.

For example, truncating a DATE down to the nearest month returns the date of the first day of the month.

Note

  • This function is overloaded. The function TRUNC can also round numbers towards zero.

  • Specifying the MILLISECOND interval with the TRUNC function is redundant, as there is nothing smaller than milliseconds.

Syntax

TRUNC( date_expr [ , interval ])

interval ::=
     YEAR | YYYY | YY
   | QUARTER | QQ | Q
   | MONTH | MM | M
   | DAY | DD | D
   | WEEK | WK | WW
   | HOUR | HH
   | MINUTE | MI | N
   | SECOND | SS | S
   | MILLISECOND | MS

Arguments

Parameter

Description

date_expr

A DATE or DATETIME expression

interval

An interval representing a date part. See the table below or the syntax reference above for valid date parts. If not specified, sets the value to to midnight and returns a DATETIME.

Valid date parts

Date part

Shorthand

Definition

YEAR

YYYY, YY

Year (0 - 9999)

QUARTER

QQ, Q

Quarter (1-4)

MONTH

MM, M

Month (1-12)

DAY

DD, D

Day of the month (1-31)

WEEK

WK, WW

Week of the year (1-52)

HOUR

HH

Hour (0-23)

MINUTE

MI, N

Minute (0-59)

SECOND

SS, S

Seconds (0-59)

MILLISECOND

MS

Milliseconds (0-999)

Returns

If no date part is specified, the return type is DATETIME. Otherwise, the return type is the same as the argument supplied.

Notes

  • All date parts work on a DATETIME.

  • The HOUR, MINUTE, SECOND, and MILLISECOND date parts work only on DATETIME. Using them on DATE will result in an error.

  • If no date part is specified, the DATE or DATETIME value will be set to midnight on the date value. See examples below

  • See also EOMONTH to find the last day of the month.

Examples

For these examples, consider the following table and contents:

CREATE TABLE cool_dates(name TEXT, d DATE, dt DATETIME);

INSERT INTO cool_dates VALUES ('Marty McFly goes back to this time','1955-11-05','1955-11-05 01:21:00.000')
    , ('Marty McFly came from this time', '1985-10-26', '1985-10-26 01:22:00.000')
    , ('Vesuvius erupts', '79-08-24', '79-08-24 13:00:00.000')
    , ('1997 begins', '1997-01-01', '1997-01-01')
    , ('1997 ends', '1997-12-31','1997-12-31 23:59:59.999');

Set all DATE columns to DATETIME at midnight

master=> SELECT name, d AS date, trunc(d) FROM cool_dates;
name                               | date       | trunc
-----------------------------------+------------+--------------------
Marty McFly goes back to this time | 1955-11-05 | 1955-11-05 00:00:00
Marty McFly came from this time    | 1985-10-26 | 1985-10-26 00:00:00
Vesuvius erupts                    | 0079-08-24 | 0079-08-24 00:00:00
1997 begins                        | 1997-01-01 | 1997-01-01 00:00:00
1997 ends                          | 1997-12-31 | 1997-12-31 00:00:00

Find the first day of the month for dates

master=> SELECT name, d AS date, trunc(d, MONTH) FROM cool_dates;
name                               | date       | trunc
-----------------------------------+------------+-----------
Marty McFly goes back to this time | 1955-11-05 | 1955-11-01
Marty McFly came from this time    | 1985-10-26 | 1985-10-01
Vesuvius erupts                    | 0079-08-24 | 0079-08-01
1997 begins                        | 1997-01-01 | 1997-01-01
1997 ends                          | 1997-12-31 | 1997-12-01

Calculate number of hours from New Years

Combine TRUNC with DATEDIFF to calculate the number of hours since New Years.

master=> SELECT name, dt AS datetime,
.               , DATEDIFF(HOUR, trunc(dt, YEAR), dt) AS "Hours since New Years"
.        FROM cool_dates;
name                               | datetime            | Hours since New Years
-----------------------------------+---------------------+----------------------
Marty McFly goes back to this time | 1955-11-05 01:21:00 |                  7393
Marty McFly came from this time    | 1985-10-26 01:22:00 |                  7153
Vesuvius erupts                    | 0079-08-24 13:00:00 |                  5653
1997 begins                        | 1997-01-01 00:00:00 |                     0
1997 ends                          | 1997-12-31 23:59:59 |                  8759