5.1 — Date Handling

What You'll Learn

• How to filter and format dates in SQL
• Common date functions and comparisons
• How this compares to Excel’s date handling

Working with Dates in SQL

Here is the sales data we'll use in our examples:

Product	Amount	SaleDate
Apples	120	2024-05-01
Apples	90	2024-05-15
Oranges	80	2024-05-03
Oranges	70	2024-06-10
Bananas	60	2024-05-20
Bananas	50	2024-06-05
Pears	40	2024-05-25
Pears	30	2024-06-15

In Excel, you might use filters or functions like YEAR(), MONTH(), or custom date formatting. SQL provides similar functions and lets you filter by date ranges.

Example: Filter sales in May 2024

SELECT *
FROM sales
WHERE SaleDate >= Date '2024-05-01' AND SaleDate <= Date '2024-05-31';

Example: Extract year and month

SELECT SaleDate, YEAR(SaleDate) AS Sale_Year, MONTH(SaleDate) AS Sale_Month
FROM sales;

• Functions like Date, YEAR(), MONTH(), and DAY() work in most databases (syntax may vary).

Grouping by Month or Year

You can group data by month or year for trend analysis:

SELECT YEAR(SaleDate) AS Sale_Year, MONTH(SaleDate) AS Sale_Month, SUM(Amount) AS Total_Sales
FROM sales
GROUP BY YEAR(SaleDate), MONTH(SaleDate)
ORDER BY Sale_Year, Sale_Month;

Formatting Dates

Some databases let you format dates for display:

SELECT DATE_FORMAT(SaleDate, '%Y-%m') AS YearMonth, SUM(Amount) AS Total
FROM sales
GROUP BY YearMonth;
-- PostgreSQL/Oracle/Teradata: TO_CHAR(SaleDate, 'YYYY-MM')
-- Snowflake: TO_VARCHAR(SaleDate, 'YYYY-MM')
-- Databricks (Spark SQL): DATE_FORMAT(SaleDate, 'yyyy-MM')

(Syntax may vary: check your database documentation for details.)

• PostgreSQL/Oracle/Teradata: TO_CHAR(SaleDate, 'YYYY-MM')
• Snowflake: TO_VARCHAR(SaleDate, 'YYYY-MM')
• Databricks (Spark SQL): DATE_FORMAT(SaleDate, 'yyyy-MM')

Common Date Functions by Database

Purpose	PostgreSQL / Oracle / Teradata	SQL Server	Databricks (Spark SQL)	Snowflake / BigQuery	MySQL / MariaDB
Extract Year	EXTRACT(YEAR FROM SaleDate) or TO_CHAR(SaleDate, 'YYYY')	YEAR(SaleDate)	year(SaleDate)	YEAR(SaleDate)	YEAR(SaleDate)
Extract Month	EXTRACT(MONTH FROM SaleDate) or TO_CHAR(SaleDate, 'MM')	MONTH(SaleDate)	month(SaleDate)	MONTH(SaleDate)	MONTH(SaleDate)
Extract Day	EXTRACT(DAY FROM SaleDate) or TO_CHAR(SaleDate, 'DD')	DAY(SaleDate)	day(SaleDate)	DAY(SaleDate)	DAY(SaleDate)
Format Date YYYY-MM	TO_CHAR(SaleDate, 'YYYY-MM')	FORMAT(SaleDate, 'yyyy-MM')	date_format(SaleDate, 'yyyy-MM')	TO_VARCHAR(SaleDate, 'YYYY-MM')	DATE_FORMAT(SaleDate, '%Y-%m')
Add Days	SaleDate + n (days) or ADD_DAYS(SaleDate, n)	DATEADD(day, n, SaleDate)	date_add(SaleDate, n)	DATEADD(day, n, SaleDate)	DATE_ADD(SaleDate, INTERVAL n DAY)
Current Date	CURRENT_DATE	GETDATE()	current_date()	CURRENT_DATE()	CURDATE()

Note: Syntax may vary slightly depending on your database version. Always check your database documentation for details.

Calculating the Number of Days Between Two Dates

You can calculate the difference in days between two dates using built-in functions. This is similar to subtracting dates in Excel.

Examples:

Database	Syntax Example
PostgreSQL / Oracle / Teradata	SELECT EndDate - StartDate AS Days_Diff FROM sales;
SQL Server	SELECT DATEDIFF(day, StartDate, EndDate) AS Days_Diff FROM sales;
Databricks (Spark SQL)	SELECT datediff(EndDate, StartDate) AS Days_Diff FROM sales;
Snowflake / BigQuery	SELECT DATEDIFF(day, StartDate, EndDate) AS Days_Diff FROM sales;
MySQL / MariaDB	SELECT DATEDIFF(EndDate, StartDate) AS Days_Diff FROM sales;

• Replace StartDate and EndDate with your actual column names.
• The result is the number of days between the two dates.

Note: The order of arguments may differ in some databases. Always check your database documentation for details.

Key Points

• SQL can filter, extract, and group by dates, just like Excel.
• Date functions and formats may differ by database.

Next Steps

Next, you’ll learn how to handle missing values (NULL) in your data.