`DATE_TRUNC`

Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API

Truncates a timestamp, timestamptz, or interval to a specified precision, such as hour, day, or month.

Syntax

1DATE_TRUNC(
2  <field>,
3  <source>
4    [, <time_zone>]
5    [, <fiscal_option> [, ...] ]
6    [, first_day_of_localized_week => <value> ]
7)

<field>: The precision to truncate to. Sets all lower-order fields to zero, or to one for day and month.

Valid values: microseconds, milliseconds, second, minute, hour, day, week, localized_week, fiscal_week, month, fiscal_month, quarter, fiscal_quarter, year, fiscal_year, decade, century, and millennium.
<source>: The value to truncate.

Valid values: timestamp, timestamptz, or interval. DATE_TRUNC casts values of type date and time to timestamp or interval, respectively.

<time_zone>: The time zone to truncate with respect to. Applies only to timestamptz input. By default, DATE_TRUNC uses the current time zone.
<fiscal_option>: A fiscal calendar option that defines the start date of the fiscal calendar. <source> must be of type date, timestamp, or timestamptz, and <field> must be fiscal_week, fiscal_month, fiscal_quarter, or fiscal_year. See Fiscal Calendar Options.
first_day_of_localized_week => <value>: A localized week option that defines the start of a week. <source> must be of type date, timestamp, or timestamptz, and <field> must be localized_week. See Localized Week Options.

Returns a timestamp, timestamptz, or interval value that matches the type of the source.

<source> can’t be of type interval when <field> is week, because a month can contain a fractional number of weeks.
You can’t provide a time zone with timestamp or interval input.
With timestamptz input, DATE_TRUNC truncates with respect to a time zone. For example, truncating to day produces the value that is midnight in that zone.

These examples assume the local time zone is America/New_York.

1SELECT DATE_TRUNC('hour', TIMESTAMP '2001-02-16 20:38:40');

Returns 2001-02-16 20:00:00.

1SELECT DATE_TRUNC('year', TIMESTAMP '2001-02-16 20:38:40');

Returns 2001-01-01 00:00:00.

1SELECT DATE_TRUNC('hour', INTERVAL '3 days 02:47:33');

Returns 3 days 02:00:00.

1SELECT DATE_TRUNC('day', timestamptz '2001-02-16 20:38:40+00');

Returns 2001-02-16 00:00:00-05.

1SELECT DATE_TRUNC('fiscal_year', TIMESTAMP '2001-02-16 20:38:40', fiscal_year_start_month => 2);

Returns 2001-02-01 00:00:00.

DID THIS ARTICLE SOLVE YOUR ISSUE?
Let us know so we can improve!