DuckDB - Date Functions

Date Functions

Version current

Version:

This section describes functions and operators for examining and manipulating date values.

Date Operators

The table below shows the available mathematical operators for DATE types.

Operator	Description	Example	Result
`+`	addition of days (integers)	`DATE '1992-03-22' + 5`	1992-03-27
`+`	addition of an `INTERVAL`	`DATE '1992-03-22' + INTERVAL 5 DAY`	1992-03-27
`-`	subtraction of `DATE`s	`DATE '1992-03-27' - DATE '1992-03-22'`	5
`-`	subtraction of an `INTERVAL`	`DATE '1992-03-27' - INTERVAL 5 DAY'`	1992-03-22

Adding to or subtracting from infinite values produces the same infinite value.

Date Functions

The table below shows the available functions for DATE types. Dates can also be manipulated with the timestamp functions through type promotion.

Function	Description	Example	Result
`current_date`	Current date (at start of current transaction)
`date_diff(``part``,` `startdate``,` `enddate``)`	The number of partition boundaries between the dates	`date_diff('month', DATE '1992-09-15', DATE '1992-11-14')`	`2`
`datediff(``part``,` `startdate``,` `enddate``)`	Alias of date_diff. The number of partition boundaries between the dates	`datediff('month', DATE '1992-09-15', DATE '1992-11-14')`	`2`
`date_part(``part``,` `date``)`	Get the subfield (equivalent to `extract`)	`date_part('year', DATE '1992-09-20')`	`1992`
`datepart(``part``,` `date``)`	Alias of date_part. Get the subfield (equivalent to `extract`)	`datepart('year', DATE '1992-09-20')`	`1992`
`date_sub(``part``,` `startdate``,` `enddate``)`	The number of complete partitions between the dates	`date_sub('month', DATE '1992-09-15', DATE '1992-11-14')`	`1`
`datesub(``part``,` `startdate``,` `enddate``)`	Alias of date_sub. The number of complete partitions between the dates	`datesub('month', DATE '1992-09-15', DATE '1992-11-14')`	`1`
`date_trunc(``part``,` `date``)`	Truncate to specified precision	`date_trunc('month', DATE '1992-03-07')`	`1992-03-01`
`datetrunc(``part``,` `date``)`	Alias of date_trunc. Truncate to specified precision	`datetrunc('month', DATE '1992-03-07')`	`1992-03-01`
`dayname(``date``)`	The (English) name of the weekday	`dayname(DATE '1992-09-20')`	`Sunday`
`extract(``part` `from` `date``)`	Get subfield from a date	`extract('year' FROM DATE '1992-09-20')`	`1992`
`greatest(``date``,` `date``)`	The later of two dates	`greatest(DATE '1992-09-20', DATE '1992-03-07')`	`1992-09-20`
`last_day(``date``)`	The last day of the corresponding month in the date	`last_day(DATE '1992-09-20')`	`1992-09-30`
`least(``date``,` `date``)`	The earlier of two dates	`least(DATE '1992-09-20', DATE '1992-03-07')`	`1992-03-07`
`make_date(``bigint``,` `bigint``,` `bigint``)`	The date for the given parts	`make_date(1992, 9, 20)`	`1992-09-20`
`monthname(``date``)`	The (English) name of the month	`monthname(DATE '1992-09-20')`	`September`
`strftime(date, format)`	Converts a date to a string according to the format string	`strftime(date '1992-01-01', '%a, %-d %B %Y')`	`Wed, 1 January 1992`

There are also dedicated extraction functions to get the subfields.

Functions applied to infinite dates will either return the same infinite dates (e.g, greatest) or NULL (e.g., date_part) depending on what “makes sense”. In general, if the function needs to examine the parts of the infinite date, the result will be NULL.