date_trunc
function
Applies to: Databricks SQL
Databricks Runtime
Returns timestamp truncated to the unit specified in unit
.
Notes
Valid units for unit
are:
‘YEAR’, ‘YYYY’, ‘YY’: truncate to the first date of the year that the
expr
falls in, the time part will be zero out‘QUARTER’: truncate to the first date of the quarter that the
expr
falls in, the time part will be zero out‘MONTH’, ‘MM’, ‘MON’: truncate to the first date of the month that the
expr
falls in, the time part will be zero out‘WEEK’: truncate to the Monday of the week that the
expr
falls in, the time part will be zero out‘DAY’, ‘DD’: zero out the time part
‘HOUR’: zero out the minute and second with fraction part
‘MINUTE’- zero out the second with fraction part
‘SECOND’: zero out the second fraction part
‘MILLISECOND’: zero out the microseconds
‘MICROSECOND’: everything remains
If the unit
is not well-formed the function returns NULL
.
Examples
> SELECT date_trunc('YEAR', '2015-03-05T09:32:05.359');
2015-01-01 00:00:00
> SELECT date_trunc('MM', '2015-03-05T09:32:05.359');
2015-03-01 00:00:00
> SELECT date_trunc('DD', '2015-03-05T09:32:05.359');
2015-03-05 00:00:00
> SELECT date_trunc('HOUR', '2015-03-05T09:32:05.359');
2015-03-05 09:00:00
> SELECT date_trunc('MILLISECOND', '2015-03-05T09:32:05.123456');
2015-03-05 09:32:05.123
-- Not a recognised unit
> SELECT date_trunc('MS', '2015-03-05T09:32:05.123456');
NULL