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 VARCHAR(40), 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