Dates are notoriously difficult in programming, and SQL is no exception. Between different formats, timezones, and database-specific functions, it's easy to get lost.
In this guide, we'll break down the essential operations you need to master: getting the current time, adding/subtracting intervals, formatting dates, and calculating differences.
Note: This playground uses SQLite. While the concepts are universal, date syntax varies heavily between databases (PostgreSQL, MySQL, SQL Server). We'll focus on SQLite syntax here but mention standard SQL equivalents.
1. Getting the Current Date and Time
The most basic operation is finding out "now".
CURRENT_DATE: Returns today's date (YYYY-MM-DD).CURRENT_TIMESTAMP: Returns date and time (YYYY-MM-DD HH:MM:SS).
Interactive Example: What day is it?
2. Date Arithmetic: Adding and Subtracting
How do you calculate a deadline 7 days from now? Or find the date 1 month ago?
In SQLite, we use modifiers with the date() or datetime() functions:
date('now', '+7 days')date('now', '-1 month')
Standard SQL: Other databases often use
DATE_ADD(date, INTERVAL 7 DAY)or simplydate + INTERVAL '7 days'.
Interactive Example: Project Deadlines
3. Formatting Dates: Extracting Parts
Often you need to group data by year, month, or day.
In SQLite, we use strftime(format, date_string):
%Y: Year (2025)%m: Month (01-12)%d: Day (01-31)%H: Hour (00-23)
Standard SQL: Many databases use
EXTRACT(YEAR FROM date)orDATE_FORMAT().
Interactive Example: Sales by Month
4. Date Differences
How many days have passed between two dates?
In SQLite, we convert dates to "Julian Day Numbers" (continuous count of days since the beginning of the Julian Period) and subtract them:
julianday(end_date) - julianday(start_date)
Standard SQL: Most databases have a
DATEDIFF(unit, start, end)function.
Interactive Challenge: Employee Tenure
You have a list of employees with their hire dates.
Challenge: Calculate how many days each employee has been working at the company (tenure). Assume "today" is '2025-12-31' for this exercise.
You have access to:
employees_date_challenge
id(INTEGER)name(TEXT)hire_date(TEXT)
Click to see the solution
SELECT
name,
hire_date,
-- Calculate difference in days
CAST(julianday('2025-12-31') - julianday(hire_date) AS INTEGER) as days_worked
FROM employees_date_challenge;
Test Your Skills with Real Interview Questions
Ready to apply your date/time knowledge? Try these real interview questions that require date manipulation:
- 30-Day Video Retention Rate (ByteDance) - Calculate retention using date arithmetic and self-joins to find users who returned exactly 30 days later
- Average Issue Resolution Time (Atlassian) - Use JULIANDAY() to calculate time differences between created and resolved dates
- Daily Trading Volume Trend (Coinbase) - Analyze time-series data with LAG() to calculate day-over-day percentage changes
- Month-over-Month Follower Growth (Twitch) - Extract year-month with STRFTIME() and use window functions to track growth
These questions test your mastery of date arithmetic, time differences, and time-series analysis in production scenarios.
Conclusion
Working with dates requires knowing your specific database's dialect. However, the core concepts—arithmetic, formatting, and extraction—are universal.
SQLite Cheat Sheet:
- Now:
CURRENT_DATE - Add/Sub:
date(col, '+N days') - Format:
strftime('%Y-%m', col) - Diff:
julianday(end) - julianday(start)
Master these, and you'll be able to handle any time-series analysis task!