Date Functions — SQL Tutorial

Working with Dates

Date and time handling is one of the most important aspects of SQL. Virtually every application deals with dates — order timestamps, user registration dates, scheduling, and reporting all depend on proper date manipulation.

SQL provides functions to get the current date and time, extract parts of a date, calculate differences between dates, and format dates for display.

NOW(): Returns the current date and time
CURDATE() / CURRENT_DATE: Returns the current date without time
CURTIME() / CURRENT_TIME: Returns the current time without date
YEAR(date): Extracts the year from a date
MONTH(date): Extracts the month number (1-12)
DAY(date): Extracts the day of the month (1-31)

Example

-- Get current date and time
SELECT NOW() AS current_datetime;
SELECT CURDATE() AS current_date;
SELECT CURTIME() AS current_time;

-- Extract parts of a date
SELECT 
    join_date,
    YEAR(join_date) AS join_year,
    MONTH(join_date) AS join_month,
    DAY(join_date) AS join_day
FROM users;

-- Find users who joined in January
SELECT username, join_date
FROM users
WHERE MONTH(join_date) = 1;

-- Find users who joined this year
SELECT username, join_date
FROM users
WHERE YEAR(join_date) = YEAR(CURDATE());

Notes

PostgreSQL uses EXTRACT(YEAR FROM date) instead of YEAR(date). The syntax: EXTRACT(MONTH FROM join_date).

DATE_FORMAT, DATEDIFF & Date Arithmetic

DATE_FORMAT lets you display dates in custom formats. DATEDIFF calculates the number of days between two dates. You can also add or subtract intervals from dates using DATE_ADD and DATE_SUB.

These functions are critical for building reports, calculating ages, tracking subscription periods, and implementing time-based business logic.

Example

-- DATE_FORMAT: Display dates in custom formats (MySQL)
SELECT 
    join_date,
    DATE_FORMAT(join_date, '%M %d, %Y') AS formatted_date,
    DATE_FORMAT(join_date, '%d/%m/%Y') AS eu_format
FROM users;

-- DATEDIFF: Days between two dates
SELECT 
    username,
    join_date,
    DATEDIFF(CURDATE(), join_date) AS days_since_joined
FROM users;

-- DATE_ADD / DATE_SUB: Add or subtract time
SELECT 
    join_date,
    DATE_ADD(join_date, INTERVAL 30 DAY) AS plus_30_days,
    DATE_SUB(join_date, INTERVAL 1 MONTH) AS minus_1_month,
    DATE_ADD(join_date, INTERVAL 1 YEAR) AS plus_1_year
FROM users;

-- Find users who joined in the last 90 days
SELECT username, join_date
FROM users
WHERE join_date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY);

Notes

DATE_FORMAT specifiers: %Y = 4-digit year, %m = month (01-12), %d = day (01-31), %M = full month name, %W = full weekday name, %H = hour, %i = minute, %s = second.