trunc function

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

Syntax 

trunc(expr, fmt)

Arguments

  • expr: A DATE expression.

  • fmt: A STRING expression specifying how to truncate.

Returns

A DATE.

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.

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