date_trunc
function
Applies to: Databricks SQL Databricks Runtime
Returns timestamp truncated to the unit specified in unit
.
Notes
Valid units for unit
are (case-insensitive):
'YEAR'
,'YYYY'
,'YY'
: truncate to the first date of the year that theexpr
falls in, the time part will be zero out'QUARTER'
: truncate to the first date of the quarter that theexpr
falls in, the time part will be zero out'MONTH'
,'MM'
,'MON'
: truncate to the first date of the month that theexpr
falls in, the time part will be zero out'WEEK'
: truncate to the Monday of the week that theexpr
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