14. Dates

Similar to working with text in SQL, working with dates can be a little bit different depending on the dialect of SQL your database uses. Unfortunately, I won't be able to cover every nuance for every database in this course. Instead, I will explain the concepts that underlie each database's approach to working with dates and provide an opportunity for you to practice with DuckDB syntax. At the end, I will provide some resources to help you translate what you've learned to the dialect of SQL you'll be using in your work.

Getting today's date

Lots of the queries you write will be saved and re-executed in the future ("hey here's that thing you did for me last February, can you update it as of today?") This means that we should prefer writing dynamic code where possible. You can always type out today's date as a literal value like '2025-07-29', but if you then want to re-use this code in the future, you'll have to remember to update the date.

Instead, it's often helpful to use a function to refer to today's date. In DuckDB, we can use the today() function to retrieve today's date:

Throughout this course, we'll use today() to refer to today's date. In other database systems, you this function is named something else (note some of these functions will return a date and a time):

• Oracle (ROI CRM) - sysdate
• SQL Server (Raiser's Edge, Blackbaud Products) - getdate()
• Postgres/Redshift (Civis) - current_date or getdate()

Adding/Subtracting Intervals from Dates

Very often, a business requirement of your work will include instructions like, "Identify all donors with a most recent contribution date in the last 12 months." We know we will need to include a where clause that uses between to test whether the transaction_date is between two dates, and that one of those dates is today (today()). But how should we get the other date, the date 12 months ago? We subtract months from today's date, like this:

select distinct account_id
from transactions
where transaction_date between today() - INTERVAL 1 YEAR and today()

Let's break this down. First, note that while there are lots of reasons to like DuckDB's syntax, date intervals are one area where the syntax diverges significantly from other SQL dialects. Most databases use a function like dateadd() or date_add() to perform this type of operation. But DuckDB's syntax is very easy to read! This query says, "Find each distinct account id from the transactions table where the transaction date is between 1 year ago and today."

Instead of YEAR, you can use DAY, MONTH, and most other date/time intervals to add/subtract intervals of time from dates. Note that when comparing dates using between, the earlier date always needs to come first. You cannot put the later date first and the earlier date second.

Exercise 1: Date subtraction

The membership department needs a list of all unique donors who have given a gift in the last 36 months. Write an SQL query to generate this list.

Exercise 2: Complex date subtraction

Membership wants a list of all donors who gave a gift in 2024 who had given another gift in the 3 months before the gift in question. In other words, identify all donors from 2024 where there exists another gift with a transaction date in the 3 months prior. Write a SQL query to identify these donors.

Date parts

Sometimes what we need to reference is part of a date (e.g., the year, the month, etc.) rather than the date itself. In Exercise 2 above, for example, we wanted to find all gifts from 2024, so we used between to do that. But wouldn't it be nice if we could just tell the database "where the year is 2024" instead of typing out full dates? Luckily we can, using the year() function:

select *
from transactions
where year(transaction_date) = 2024

Not all SQL dialects support year() but the majority do. Some will instead use functions like datepart(), date_part(), or trunc(). In this course, we will be using year(), along with month() and day(), to extract the individual year, month, and day numbers from dates. Each of these functions does pretty much what you'd expect. Here are the results for today's date:

Exercise 3: Date parts

The development team is going to run a special "leap day" ask. As part of this campaign, they would like a list of all donors who have ever given on February 29, regardless of the year. Write an SQL query to identify these donors.

Other Resources

This site contains some helpful information about working with dates in different dialects of SQL.