Date Functions
Copy Markdown
This section describes functions and operators for examining and manipulating DATE
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 00:00:00
+addition of a variable INTERVAL
SELECT DATE '1992-03-22' + INTERVAL ( d.days ) DAY FROM ( VALUES ( 5 ), ( 11 )) d ( days ) 1992-03-27 00:00:00 and 1992-04-02 00:00:00
-subtraction of DATEs
DATE '1992-03-27' - DATE '1992-03-22' 5
-subtraction of an INTERVAL
DATE '1992-03-27' - INTERVAL 5 DAY 1992-03-22 00:00:00
-subtraction of a variable INTERVAL
SELECT DATE '1992-03-27' - INTERVAL ( d.days ) DAY FROM ( VALUES ( 5 ), ( 11 )) d ( days ) 1992-03-22 00:00:00 and 1992-03-16 00:00:00
Adding to or subtracting from infinite values produces the same infinite value.
The table below shows the available functions for DATE types.
Dates can also be manipulated with the timestamp functions through type promotion.
Name
Description
date_add(date, interval)Add the interval to the date and return a DATETIME value.
date_diff(part, startdate, enddate)The number of partstartdate and enddate, inclusive of the larger date and exclusive of the smaller date.
date_part(part, date)Get subfield (equivalent to extract).
date_sub(part, startdate, enddate)The signed length of the interval between startdate and enddate, truncated to whole multiples of part
date_trunc(part, date)Truncate to specified precision .
dayname(date)The (English) name of the weekday.
days_in_month(date)The number of days in the month of the given date.
extract(part from date)Get subfield from a date.
greatest(date, date)The later of two dates.
isfinite(date)Returns true if the date is finite, false otherwise.
isinf(date)Returns true if the date is infinite, false otherwise.
julian(date)Extract the Julian Day number from a date.
last_day(date)The last day of the corresponding month in the date.
least(date, date)The earlier of two dates.
make_date(year, month, day)The date for the given parts.
monthname(date)The (English) name of the month.
strftime(date, format)Converts a date to a string according to the format string .
time_bucket(bucket_width, date[, offset])Truncate date to a grid of width bucket_width. The grid is anchored at 2000-01-01[ + offset] when bucket_width is a number of months or coarser units, else 2000-01-03[ + offset]. Note that 2000-01-03 is a Monday.
time_bucket(bucket_width, date[, origin])Truncate timestamptz to a grid of width bucket_width. The grid is anchored at the origin timestamp, which defaults to 2000-01-01 when bucket_width is a number of months or coarser units, else 2000-01-03. Note that 2000-01-03 is a Monday.
today()Current date (start of current transaction) in the local time zone.
Description Add the interval to the date and return a DATETIME value.
Example date_add(DATE '1992-09-15', INTERVAL 2 MONTH)
Result 1992-11-15 00:00:00
Description The number of partstartdate and enddate, inclusive of the larger date and exclusive of the smaller date.
Example date_diff('month', DATE '1992-09-15', DATE '1992-11-14')
Result 2
Alias datediff
Description Get the subfield (equivalent to extract).
Example date_part('year', DATE '1992-09-20')
Result 1992
Alias datepart
Description The signed length of the interval between startdate and enddate, truncated to whole multiples of part
Example date_sub('month', DATE '1992-09-15', DATE '1992-11-14')
Result 1
Alias datesub
Description Truncate to specified precision . Always returns a TIMESTAMP, even when the input is a DATE.
Example date_trunc('month', DATE '1992-03-07')
Result 1992-03-01 00:00:00
Alias datetrunc
Description The (English) name of the weekday.
Example dayname(DATE '1992-09-20')
Result Sunday
Description The number of days in the month of the given date.
Example days_in_month(DATE '1992-02-15')
Result 29
Description Get subfield from a date.
Example extract('year' FROM DATE '1992-09-20')
Result 1992
Description The later of two dates.
Example greatest(DATE '1992-09-20', DATE '1992-03-07')
Result 1992-09-20
Description Returns true if the date is finite, false otherwise.
Example isfinite(DATE '1992-03-07')
Result true
Description Returns true if the date is infinite, false otherwise.
Example isinf(DATE '-infinity')
Result true
Description Extract the Julian Day number from a date.
Example julian(DATE '1992-09-20')
Result 2448886.0
Description The last day of the corresponding month in the date.
Example last_day(DATE '1992-09-20')
Result 1992-09-30
Description The earlier of two dates.
Example least(DATE '1992-09-20', DATE '1992-03-07')
Result 1992-03-07
Description The date for the given parts.
Example make_date(1992, 9, 20)
Result 1992-09-20
Description The (English) name of the month.
Example monthname(DATE '1992-09-20')
Result September
Description Converts a date to a string according to the format string .
Example strftime(DATE '1992-01-01', '%a, %-d %B %Y')
Result Wed, 1 January 1992
Description Truncate date to a grid of width bucket_width. The grid is anchored at 2000-01-01[ + offset] when bucket_width is a number of months or coarser units, else 2000-01-03[ + offset]. Note that 2000-01-03 is a Monday.
Example time_bucket(INTERVAL '2 months', DATE '1992-04-20', INTERVAL '1 month')
Result 1992-04-01
Description Truncate timestamptz to a grid of width bucket_width. The grid is anchored at the origin timestamp, which defaults to 2000-01-01 when bucket_width is a number of months or coarser units, else 2000-01-03. Note that 2000-01-03 is a Monday.
Example time_bucket(INTERVAL '2 weeks', DATE '1992-04-20', DATE '1992-04-01')
Result 1992-04-15
Description Current date (start of current transaction) in the local time zone.
Example today()
Result 2022-10-08
Alias current_date (no parentheses necessary)
There are also dedicated extraction functions to get the subfields .
A few examples include extracting the day from a date, or the day of the week from a date.
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.
© 2026 DuckDB Foundation, Amsterdam NL
