DuckDB is an in-process SQL OLAP database management system designed for analytical workloads. It is known for its speed, efficiency, and ease of use. One of the many functions DuckDB provides is current_date, which is useful for working with date-related data.

In this article, we’ll look at how the current_date function works, along with some straightforward examples.

What is the current_date Function?

In DuckDB, current_date is a built-in SQL function that returns the current date as a DATE type.

The current_date function does not require (or accept) any arguments and it automatically retrieves the current date (at start of current transaction) in UTC. It can be useful for filtering, comparing, or inserting dates in your database operations.

Syntax

The syntax for the current_date function is straightforward:

current_date

So, no parentheses are required.

However, it’s also possible to append parentheses:

current_date()

Both options produce the same output.

When executed, the function returns the current date in the format YYYY-MM-DD.

Example

The current_date function fetches the current date in UTC. It’s based on the start of current transaction.

Example:

SELECT current_date;

Result:

+--------------+
| current_date |
+--------------+
| 2025-02-10   |
+--------------+

Adding Parentheses

Most SQL functions require parentheses, but we can see from the previous example that current_date does not. That said, we can still use parentheses with current_date:

SELECT current_date();

Result:

+----------------+
| current_date() |
+----------------+
| 2025-02-10     |
+----------------+

No error, and the same result.

Adding to the Current Date

We can combine current_date with date operators to add a specified time interval to the current date:

SELECT 
    current_date,
    current_date + INTERVAL 10 DAY;

Result:

+--------------+----------------------------------------------------------------------+
| current_date | (current_date + to_days(CAST(trunc(CAST(10 AS DOUBLE)) AS INTEGER))) |
+--------------+----------------------------------------------------------------------+
| 2025-02-10   | 2025-02-20 00:00:00                                                  |
+--------------+----------------------------------------------------------------------+

When we added to the date, the result is a timestamp/datetime value, as opposed to just the date.

Also, the header of the second column implies that DuckDB used the to_days() function, along with trunc() and CAST() to produce the result.

So we could use that method too:

SELECT (current_date + to_days(CAST(trunc(CAST(10 AS DOUBLE)) AS INTEGER)));

Result:

+----------------------------------------------------------------------+
| (current_date + to_days(CAST(trunc(CAST(10 AS DOUBLE)) AS INTEGER))) |
+----------------------------------------------------------------------+
| 2025-02-20 00:00:00                                                  |
+----------------------------------------------------------------------+

However, using the INTERVAL operator was a lot more concise.

Subtracting from the Current Date

Here’s a similar example, but this time we subtract 10 days instead of adding it:

SELECT 
    current_date(),
    current_date - INTERVAL 10 DAY;

Result:

+----------------+----------------------------------------------------------------------+
| current_date() | (current_date - to_days(CAST(trunc(CAST(10 AS DOUBLE)) AS INTEGER))) |
+----------------+----------------------------------------------------------------------+
| 2025-02-10     | 2025-01-31 00:00:00                                                  |
+----------------+----------------------------------------------------------------------+

No Arguments

No arguments are accepted. Here’s what happens when we pass an argument:

SELECT current_date(1);

Result:

Binder Error: No function matches the given name and argument types 'current_date(INTEGER_LITERAL)'. You might need to add explicit type casts.
	Candidate functions:
	current_date() -> DATE

LINE 1: SELECT current_date(1);
               ^

The today() Function

DuckDB also has a today() function that does the same thing as current_date:

SELECT 
    current_date,
    today();

Result:

+--------------+------------+
| current_date |  today()   |
+--------------+------------+
| 2025-02-10   | 2025-02-10 |
+--------------+------------+