# 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
```