
What makes a good SQL coder is the ability to manipulate Data in any way that they like – a big part of that is being able to manipulate dates. Dates are extremely important because businesses like to compare and assess business performance across different periods of time. Therefore, being able to manipulate dates is essential for top-tier business operations and business reporting.
In this article, we’re going to dive into 5 of the most important and most useful DATE functions in SQL along with some practical business cases in which they can be used.
1. DATE_TRUNC()
Syntax:
DATE_TRUNC(date_expression, date_part)
What does it do?
DATE_TRUNC() shortens the date to the specified date part.
In the example below, since we specified date_part = MONTH, the date_trunc() will truncate the date to get the first day of the month:
DATE_TRUNC('2021-06-28', MONTH) = '2021-06-01'
To give another example, if we specified date_part = YEAR, then we would get the first day of the year of the specified date:
DATE_TRUNC('2021-06-28', YEAR) = '2021-01-01'
When is it Useful?
- DATE_TRUNC is extremely useful when you want to aggregate numbers on a periodic basis, such as a weekly, monthly, or yearly basis
- DATE_TRUNC is necessary when conducting cohort analyses, where you typically group users by month
Example(s)
Example 1: Suppose you wanted to get a weekly sum of sales from the table below:

with sales_data as (
SELECT DATE_TRUNC(date, WEEK) as week_date,
daily_sales
FROM sales
)
SELECT week_date,
SUM(daily_sales) as weekly_sales
FROM sales_data
GROUP BY week_date
2. DATE_DIFF()
Syntax:
DATE_DIFF(date_expression_1, date_expression_2, date_part)
What does it do?
DATE_DIFF() compares two dates and returns the difference in date part between the two dates.
For example, if date_part = DAY, then DATE_DIFF() returns the number of DAYs between the two dates. If date_part = MONTH, then DATE_DIFF() returns the number of MONTHs between the two dates.
DATE_DIFF('2021-01-02', '2021-01-01', DAY) = 1
When is it Useful?
- DATE_DIFF() is useful when you want to compare two dates, for example, when a package was shipped and when a package was delivered, or when a user registered and when they canceled.
- DATE_DIFF() is also useful in the WHERE clause if you want to filter dates that happened X periods ago (eg. 5 days ago, 2 weeks ago, last month).
Example(s)
Example 1: Suppose you wanted to get the time between when a package was shipped and when a package was delivered:

SELECT order_id
, DATE_DIFF(date_received, date_shipped, DAY) as shipping_time
FROM orders
Example 2: Using the same table above, suppose you wanted to get all orders where the shipping time was less than 10 days:
SELECT order_id
, amount
FROM orders
WHERE DATE_DIFF(date_received, date_shipped, DAY) < 10
3. DATE_ADD() / DATE_SUB()
Syntax:
DATE_ADD(date_expression, INTERVAL int64 date_part)
DATE_SUB(date_expression, INTERVAL int64 date_part)
What does it do?
DATE_ADD() adds a specified number of date parts to a date. Conversely, DATE_SUB subtracts a specified number of date parts to a date.
DATE_ADD('2021-01-01', INTERVAL 3 DAY) = '2021-01-04'
DATE_SUB('2021-01-04', INTERVAL 3 DAY) = '2021-01-01'
DATE_ADD('2021-01-01', INTERVAL 1 MONTH) = '2021-02-01'
When is it Useful?
- DATE_ADD() and DATE_SUB() can be similarly used like DATE_DIFF() in the WHERE clause to filter dates that happened X periods ago or X periods in the future
Example(s):
Example 1: Suppose you wanted to get all orders where the shipping time was less than 10 days:

SELECT order_id
, amount
FROM orders
WHERE DATE_ADD(date_shipped, INTERVAL 10 DAY) > date_received
4. EXTRACT()
Syntax:
EXTRACT(part FROM date_expression)
What does it do?
EXTRACT() returns the value that corresponds to the specified date part.
EXTRACT(DAY FROM '2021-01-03') = 3
EXTRACT(MONTH FROM '2021-01-03') = 1
EXTRACT(YEAR FROM '2021-01-03') = 2021
When is it Useful?
- Using EXTRACT() is an easy way to get specific components of a date that you’re interested in
- For example, if your company does reporting by week numbers, you can use EXTRACT() to get the week number for a given date for a given record
- EXTRACT() allows you to get the month number or year from the date, which can be used as features for a machine learning model
5. CURRENT_DATE()
Syntax:
CURRENT_DATE([time_zone])
What does it do?
CURRENT_DATE() returns the current date in which the query was executed in the specified timezone. Note that the timezone parameter is optional and does not need to be specified.
When is it Useful?
- Using CURRENT_DATE() is an easier way of referencing today’s date as opposed to a hardcoded date, which is especially useful if it’s a query that is scheduled on Airflow or a query that you use often
Example(s):
Example 1: Suppose you wanted to get all orders that were shipped in the past week:

SELECT order_id
, amount
FROM orders
WHERE DATE_DIFF(CURRENT_DATE(), date_shipped, DAY) < 7
Example 2: Suppose you wanted to get all orders in any year that were shipped in the same month as today’s date:
SELECT order_id
, amount
FROM orders
WHERE EXTRACT(MONTH FROM date_shipped) = EXTRACT(MONTH FROM CURRENT_DATE())
Thanks for Reading!
Now that you understand how the top five DATE functions in Sql work, it’s time to put your knowledge to the test. There are two resources that I will provide:
- Leetcode is a website that has hundreds of coding problems that you can solve. One problem called ‘Rising Temperature‘ is a good problem that challenges your ability to work with dates.
- w3resource is another great resource – in this link, there are 21 problems focused on datetime.
As always, I wish you the best in your learning endeavors!
Not sure what to read next? I’ve picked another article for you:
Top Five SQL Window Functions You Should Know For Data Science Interviews
and another one!
10 Statistical Concepts You Should Know For Data Science Interviews
Terence Shin
- If you enjoyed this, follow me on Medium for more
- Interested in collaborating? Let’s connect on LinkedIn