Tutorials›SQL›Date Functions
🟢 Free Demo
SQL TutorialTopic 18 of 20

Date Functions

Work with dates — extract, calculate, format

✅ What You Will Learn

›How to extract year, month, and day from a date column
›How to calculate the difference between two dates
›How to get today's date and current timestamp
›How to format dates for display
›How date functions differ between MySQL, SQL Server, and PostgreSQL

Date functions extract or calculate date-related values — the year, month, or day from a date column, the difference between two dates, or the current date and time. Date analysis is central to almost every analytics project: trend analysis, cohort analysis, aging reports, and time-series all rely on date functions.

📋 The orders table — date functions extract parts of order_date

order_idcustomer_nameproductamountorder_date
1001Rahul SharmaLaptop450002026-01-15
1002Priya VermaMobile Phone180002026-01-16
1003Amit KumarHeadphones35002026-02-03
1004Sneha KapoorLaptop520002026-02-17
1005Vikram SinghTablet280002026-03-05

Syntax

SQL SYNTAX
-- Get current date/time
NOW()              -- MySQL/PostgreSQL: current datetime
GETDATE()          -- SQL Server: current datetime
CURRENT_DATE       -- current date only

-- Extract parts of a date
YEAR(date)
MONTH(date)
DAY(date)

-- Date difference
DATEDIFF(end_date, start_date)    -- MySQL
DATEDIFF(day, start_date, end_date) -- SQL Server

Examples

Example 1Extract year and month from a date
SELECT order_date,
       YEAR(order_date)  AS order_year,
       MONTH(order_date) AS order_month,
       DAY(order_date)   AS order_day
FROM orders;
OUTPUT
order_date  | order_year | order_month | order_day
------------|------------|-------------|----------
2026-01-15  | 2026       | 1           | 15
Example 2Monthly revenue trend
SELECT YEAR(order_date)  AS year,
       MONTH(order_date) AS month,
       SUM(amount) AS monthly_revenue,
       COUNT(*) AS orders
FROM orders
GROUP BY YEAR(order_date), MONTH(order_date)
ORDER BY year, month;
💡

Grouping by YEAR + MONTH together gives you one row per calendar month — the standard for monthly trend reports.

Example 3Calculate order age in days
-- MySQL
SELECT order_id,
       order_date,
       DATEDIFF(CURRENT_DATE, order_date) AS days_since_order
FROM orders;

-- SQL Server
SELECT order_id,
       order_date,
       DATEDIFF(day, order_date, GETDATE()) AS days_since_order
FROM orders;
Example 4Filter by recent dates
-- Orders from the last 30 days (MySQL)
SELECT * FROM orders
WHERE order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY);

-- Orders from the last 30 days (SQL Server)
SELECT * FROM orders
WHERE order_date >= DATEADD(day, -30, GETDATE());

📌 Key Points to Remember

  • ✓YEAR(), MONTH(), DAY() extract parts from a date — useful for grouping
  • ✓GROUP BY YEAR() and MONTH() together for monthly trend reports
  • ✓DATEDIFF syntax differs between MySQL and SQL Server — check your database
  • ✓Always filter dates as date values, not as text strings
  • ✓Date functions are essential for trend analysis, aging reports, and cohort analysis

🏢 Real-World Application

Date functions are essential for any time-series reporting. Monthly revenue reports use MONTH(order_date) and YEAR(order_date) for GROUP BY. Customer age calculation for KYC uses DATEDIFF (today, date_of_birth). Reports showing "orders in last 30 days" filter with WHERE order_date >= DATEADD(DAY, -30, GETDATE()). Cohort analysis groups users by signup month using DATEPART(MONTH, signup_date). Every analytics role deals with dates constantly — quarterly reports, year-over-year comparisons, and rolling 7-day or 30-day metrics all rely on date functions.

⚠️ Common Mistakes to Avoid

WRONGUsing date functions that differ between databases without checking
FIXGETDATE() works in SQL Server but not MySQL (use NOW()). DATEADD works in SQL Server; MySQL uses DATE_ADD. Always check the function name for your specific database.
WRONGStoring dates as text strings and then filtering with date functions
FIXDates stored as VARCHAR cannot be reliably sorted or compared with date functions. Always store dates in DATE or DATETIME columns. If you inherit text-stored dates, cast them: CAST(col AS DATE).
WRONGFiltering dates with = instead of a range
FIXWHERE order_date = '2026-01-15' may miss rows with a time component. Use WHERE order_date >= '2026-01-15' AND order_date < '2026-01-16' or CAST(order_date AS DATE) = '2026-01-15' to be safe.

❓ Frequently Asked Questions

How do I get the current date in SQL?

It varies by database: GETDATE() in SQL Server, NOW() or CURDATE() in MySQL, CURRENT_DATE in PostgreSQL and standard SQL. GETDATE() returns date + time; CURDATE() returns only the date portion.

How do I extract the month and year from a date in SQL?

In SQL Server and MySQL: YEAR(date_column) and MONTH(date_column). In PostgreSQL: EXTRACT(YEAR FROM date_column) and EXTRACT(MONTH FROM date_column). GROUP BY YEAR(order_date), MONTH(order_date) is a standard monthly report pattern.

How do I calculate the number of days between two dates?

In SQL Server: DATEDIFF(DAY, start_date, end_date). In MySQL: DATEDIFF(end_date, start_date). In PostgreSQL: end_date - start_date (date subtraction returns an integer number of days).

How do I filter records from the last 30 days in SQL?

SQL Server: WHERE order_date >= DATEADD(DAY, -30, GETDATE()). MySQL: WHERE order_date >= DATE_SUB(NOW(), INTERVAL 30 DAY). PostgreSQL: WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'.

How do I format a date for display in SQL?

SQL Server: FORMAT(date_col, 'dd/MM/yyyy'). MySQL: DATE_FORMAT(date_col, '%d/%m/%Y'). PostgreSQL: TO_CHAR(date_col, 'DD/MM/YYYY'). Formatting is for display only and should be applied at the SELECT stage, not in WHERE or GROUP BY.

✏️ Practice Exercise

Write a query to find the total revenue and number of orders for each month in 2026, sorted by month.

← PreviousString FunctionsNext →Window Functions
🎓 Level Up Faster

Learn SQL with Live Trainer Guidance

These tutorials give you the theory. Our live SQL course at EVIKA Academy, Noida teaches you to apply SQL on real company datasets — with a trainer who uses it daily at MakeMyTrip.