Date Truncation is incredibly useful for aggregating, analyzing, and reporting data over specific time intervals, such as years, months, days, or even hours and minutes. By “truncating” a date, you essentially round it down to the nearest specified unit, which can simplify comparisons, aggregations, and trend analysis over periods of time.
DATE_TRUNC( <date_or_time_part>, <date_or_time_expr> )The DATE_TRUNC function works by taking two arguments: the unit of time to which you want to truncate the date, and the date or timestamp value itself. The function then returns a new date value rounded down to the closest specified time unit, effectively “normalizing” your date data to that level of granularity.
For example, to find the current month for the lead created date you would write SQL like this:
DATE_TRUNC('months', "CreatedDate") “Lead Created Month”
RESULT EXAMPLE (assuming Created Date is ‘2024-02-25’)
2024-02-01