trunc function

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Returns a date with the date truncated to the unit specified by the format model unit.

Syntax 

trunc(expr, unit)

Arguments

  • expr: A DATE expression.

  • unit: A STRING expression specifying how to truncate.

Returns

A DATE.

Notes

fmt must be one of (case-insensitive):

  • 'YEAR', 'YYYY', 'YY' - truncate to the first date of the year that the date falls in.

  • 'QUARTER' - truncate to the first date of the quarter that the date falls in.

  • 'MONTH', 'MM', 'MON' - truncate to the first date of the month that the date falls in.

  • 'WEEK' - truncate to the Monday of the week that the date falls in.

If fmt is not well-formed the function returns NULL.

Examples 

> SELECT trunc('2019-08-04', 'week');
 2019-07-29

> SELECT trunc('2019-08-04', 'quarter');
 2019-07-01

> SELECT trunc('2009-02-12', 'MM');
 2009-02-01

> SELECT trunc('2015-10-27', 'YEAR');
 2015-01-01

-- 'JAHR' is not a recognized unit
> SELECT trunc('2015-10-27', 'JAHR');
 NULL