Almost every real database has dates in it: order dates, invoice dates, due dates, login times. And almost every report asks a date question: this month’s sales, overdue invoices, customers who joined in the last 30 days.
This guide covers the date functions you’ll use most, in both SQL Server and MySQL, plus the two mistakes that cause wrong totals and slow queries.

Table of Contents
1. Get the current date and time
| You want | SQL Server | MySQL |
|---|---|---|
| Date and time now | GETDATE() or SYSDATETIME() | NOW() |
| Today’s date only | CAST(GETDATE() AS date) | CURDATE() |
-- SQL Server
SELECT GETDATE() AS RightNow, CAST(GETDATE() AS date) AS Today;
-- MySQL
SELECT NOW() AS RightNow, CURDATE() AS Today;
SYSDATETIME() is more precise than GETDATE() (it returns datetime2). Both use the server’s clock and time zone, which may not be your users’ time zone.
2. Add or subtract days, months and years
-- SQL Server: DATEADD(unit, number, date)
SELECT DATEADD(day, 30, '2026-10-07'); -- 2026-11-06
SELECT DATEADD(month, -1, '2026-10-07'); -- 2026-09-07
SELECT DATEADD(year, 1, '2026-10-07'); -- 2027-10-07
-- MySQL: DATE_ADD(date, INTERVAL number unit)
SELECT DATE_ADD('2026-10-07', INTERVAL 30 DAY);
SELECT DATE_SUB('2026-10-07', INTERVAL 1 MONTH);
Adding months to the end of a month is clamped to a valid date: adding 1 month to 31 January gives 28 February (or 29 in a leap year) in both databases.
3. Difference between two dates
-- SQL Server: DATEDIFF(unit, start, end) = end - start
SELECT DATEDIFF(day, '2026-10-01', '2026-10-07'); -- 6
-- MySQL: DATEDIFF(end, start) returns days only
SELECT DATEDIFF('2026-10-07', '2026-10-01'); -- 6
-- for other units, TIMESTAMPDIFF(unit, start, end)
SELECT TIMESTAMPDIFF(MONTH, '2026-01-15', '2026-10-07'); -- 8
Watch the argument order. SQL Server puts the start date first, while MySQL’s DATEDIFF puts the end date first. Swap them and you get negative numbers.
Another SQL Server surprise: DATEDIFF counts how many boundaries are crossed, not full periods:
SELECT DATEDIFF(year, '2025-12-31', '2026-01-01'); -- 1, after just one day!
So don’t calculate someone’s age or an asset’s years in service with DATEDIFF(year, ...) alone. Compare the full dates, or count days or months and then divide.
4. Get part of a date
| You want | SQL Server | MySQL |
|---|---|---|
| Year, month, day | YEAR(d), MONTH(d), DAY(d) | YEAR(d), MONTH(d), DAY(d) |
| Any part | DATEPART(quarter, d) | QUARTER(d) or EXTRACT(QUARTER FROM d) |
| Month name | DATENAME(month, d) | MONTHNAME(d) |
| Weekday name | DATENAME(weekday, d) | DAYNAME(d) |
These are perfect for the SELECT list and for GROUP BY, for example monthly totals:
SELECT YEAR(OrderDate) AS Yr, MONTH(OrderDate) AS Mon, SUM(Amount) AS Total
FROM Orders
GROUP BY YEAR(OrderDate), MONTH(OrderDate)
ORDER BY Yr, Mon;
5. First and last day of a month
-- SQL Server (2012 and later)
SELECT EOMONTH('2026-10-07'); -- 2026-10-31
SELECT DATEFROMPARTS(2026, 10, 1); -- 2026-10-01
SELECT DATEADD(day, 1, EOMONTH('2026-10-07', -1)); -- first day of this month
-- MySQL
SELECT LAST_DAY('2026-10-07'); -- 2026-10-31
SELECT DATE_FORMAT('2026-10-07', '%Y-%m-01'); -- 2026-10-01
Month-end and period-close reports use these constantly.
6. Format a date as text
-- SQL Server
SELECT CONVERT(varchar(10), GETDATE(), 103); -- 07/10/2026 (dd/mm/yyyy)
SELECT CONVERT(varchar(10), GETDATE(), 23); -- 2026-10-07
SELECT FORMAT(GETDATE(), 'dd MMM yyyy'); -- 07 Oct 2026
-- MySQL
SELECT DATE_FORMAT(NOW(), '%d/%m/%Y'); -- 07/10/2026
SELECT DATE_FORMAT(NOW(), '%d %b %Y'); -- 07 Oct 2026
Two tips. First, formatting is usually better done in the application or report, not in SQL. Keep dates as dates for as long as possible. Second, in SQL Server, FORMAT() is convenient but noticeably slower than CONVERT() on large result sets.
Mistake 1: BETWEEN with date and time values
-- Looks right, but misses most of 31 October!
SELECT SUM(Amount) FROM Orders
WHERE OrderDate BETWEEN '2026-10-01' AND '2026-10-31';
If OrderDate stores a time, then '2026-10-31' means 31 October at 00:00:00. An order at 31 October 09:15 is after that, so it’s left out, and your monthly total is wrong.
Fix: use “greater than or equal to the start, and less than the day after the end”:
SELECT SUM(Amount) FROM Orders
WHERE OrderDate >= '2026-10-01'
AND OrderDate < '2026-11-01';
This works for date, datetime and datetime2 columns, and you never have to think about 23:59:59.997.
Mistake 2: functions on the column in WHERE
-- Slow on a big table: can't use the index on OrderDate
WHERE YEAR(OrderDate) = 2026 AND MONTH(OrderDate) = 10
-- Fast: a plain range the index can seek on
WHERE OrderDate >= '2026-10-01' AND OrderDate < '2026-11-01'
Both return the same rows. But when you wrap the column in a function, the database has to calculate that function for every row, so it can’t use an index seek. Functions are fine in SELECT and GROUP BY. In WHERE, keep the column bare. I explain why in Index Seek vs Index Scan: Why SQL Server Ignores Your Index.
Ready-to-use date filters
-- SQL Server
DECLARE @today date = CAST(GETDATE() AS date);
-- Today
WHERE OrderDate >= @today AND OrderDate < DATEADD(day, 1, @today)
-- Last 30 days
WHERE OrderDate >= DATEADD(day, -30, @today)
-- This month
WHERE OrderDate >= DATEADD(day, 1, EOMONTH(@today, -1))
AND OrderDate < DATEADD(day, 1, EOMONTH(@today))
-- Overdue invoices
WHERE DueDate < @today AND PaidDate IS NULL
-- MySQL
-- Today
WHERE OrderDate >= CURDATE() AND OrderDate < CURDATE() + INTERVAL 1 DAY
-- Last 30 days
WHERE OrderDate >= CURDATE() - INTERVAL 30 DAY
-- This month
WHERE OrderDate >= DATE_FORMAT(CURDATE(), '%Y-%m-01')
AND OrderDate < DATE_FORMAT(CURDATE(), '%Y-%m-01') + INTERVAL 1 MONTH
Which date type should a column use?
datewhen there’s no time: birth dates, invoice dates, due dates.datetime2(SQL Server) ordatetime(MySQL) when you need the time: created-at, login time.- Never store dates as text (
varchar).'07/10/2026'sorts wrongly, can’t be validated, and every comparison needs a conversion.
More in SQL Data Types Explained Simply.
Date Functions in Real Reports
Business reports bring a few date problems that the basic functions don’t solve on their own. These are the ones that come up most.
Show days with no sales
A daily sales report built with GROUP BY sale_date simply skips days with no sales, so a chart jumps from the 1st to the 3rd and nobody notices the gap. The fix is to generate every date first, then LEFT JOIN the sales to it:
-- MySQL 8+ / SQL Server: a recursive CTE builds the list of days
WITH RECURSIVE days AS ( -- SQL Server: leave out the word RECURSIVE
SELECT DATE '2026-03-01' AS d -- SQL Server: CAST('2026-03-01' AS date)
UNION ALL
SELECT d + INTERVAL 1 DAY -- SQL Server: DATEADD(day, 1, d)
FROM days
WHERE d < '2026-03-05'
)
SELECT days.d AS sale_date, COALESCE(SUM(s.amount), 0) AS total
FROM days
LEFT JOIN sales s ON s.sale_date = days.d
GROUP BY days.d
ORDER BY days.d;
-- PostgreSQL: generate_series does it in one line
SELECT d::date AS sale_date, COALESCE(SUM(s.amount), 0) AS total
FROM generate_series(DATE '2026-03-01', DATE '2026-03-05', INTERVAL '1 day') AS d
LEFT JOIN sales s ON s.sale_date = d::date
GROUP BY d
ORDER BY d;
| sale_date | total |
|---|---|
| 2026-03-01 | 150.00 |
| 2026-03-02 | 0.00 |
| 2026-03-03 | 80.00 |
| 2026-03-04 | 0.00 |
| 2026-03-05 | 40.00 |
In SQL Server, a recursive CTE stops after 100 levels by default, so a full year fails. Add OPTION (MAXRECURSION 366) at the end of the query, or use GENERATE_SERIES in SQL Server 2022. Many reporting databases keep a permanent calendar table instead, with one row per day and columns for month, quarter, fiscal year, weekday and public holidays. It makes all of these reports simpler.
Fiscal years that don’t start in January
If your financial year runs from 1 April to 31 March, “fiscal year 2026” starts on 1 April 2026. A neat trick: move the date back by the number of months before the year starts, then take the year:
-- Fiscal year starting in April (3 months after January)
SELECT YEAR(DATEADD(month, -3, invoice_date)) AS fiscal_year -- SQL Server
SELECT YEAR(invoice_date - INTERVAL 3 MONTH) AS fiscal_year -- MySQL
SELECT EXTRACT(YEAR FROM invoice_date - INTERVAL '3 months') -- PostgreSQL
-- 2026-03-31 -> 2025
-- 2026-04-01 -> 2026
Change the 3 to match your year: 6 for a year starting in July, 9 for October. Put the calculation in a view or a calendar table so every report uses the same rule.
Store UTC, show local time
If your system has users or servers in more than one time zone, or runs in the cloud, store timestamps in UTC and convert them for display. Otherwise “orders placed today” means different things on different servers. Converting a UTC time to Bahrain time (UTC+3):
-- SQL Server 2016+
SELECT created_at_utc AT TIME ZONE 'UTC' AT TIME ZONE 'Arab Standard Time'
-- MySQL (named zones need the time zone tables loaded; an offset always works)
SELECT CONVERT_TZ(created_at_utc, '+00:00', '+03:00')
-- PostgreSQL (timestamptz column)
SELECT created_at AT TIME ZONE 'Asia/Bahrain'
In SQL Server, the first AT TIME ZONE 'UTC' tells it the stored value is UTC; the second converts it. SQL Server uses Windows zone names (SELECT * FROM sys.time_zone_info lists them), while PostgreSQL uses names such as Asia/Bahrain. For dates with no time, such as invoice and due dates, use a plain date column and don’t convert at all.
Week numbers and the first day of the week
“Week 1” and “the start of the week” differ between countries and databases. In SQL Server, DATEPART(weekday, ...) depends on the SET DATEFIRST setting, which follows the login’s language, so the same query can give different answers for different users. For week numbers, ask for ISO weeks explicitly, which always start on Monday:
| Database | ISO week number |
|---|---|
| SQL Server | DATEPART(iso_week, order_date) |
| MySQL | WEEK(order_date, 3) |
| PostgreSQL | EXTRACT(WEEK FROM order_date) (always ISO) |
If your business week starts on Sunday or Saturday, as it does in much of the Gulf, define it once in a calendar table rather than relying on server settings.
Frequently Asked Questions
How do I get just the date from a datetime?
SQL Server: CAST(created_at AS date). MySQL: DATE(created_at). PostgreSQL: created_at::date. For filtering, though, keep using a range on the original column (>= today and < tomorrow), so an index on it can still be used.
How do I calculate someone’s age in years?
In MySQL, TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) counts full years correctly. In PostgreSQL, EXTRACT(YEAR FROM age(birth_date)). In SQL Server, DATEDIFF(year, ...) counts year boundaries, so subtract one if the birthday hasn’t happened yet this year: DATEDIFF(year, BirthDate, @today) - CASE WHEN DATEADD(year, DATEDIFF(year, BirthDate, @today), BirthDate) > @today THEN 1 ELSE 0 END.
Why does my date filter miss rows from the last day?
Because the column has a time part and the filter ends at midnight. BETWEEN '2026-03-01' AND '2026-03-31' stops at 31 March 00:00:00. Use >= '2026-03-01' AND < '2026-04-01' instead, as shown in Mistake 1 above.
What date format should I use in SQL?
Write dates as 'YYYY-MM-DD' (for example '2026-03-01'). It works for date and datetime2 columns in every database, while '03/01/2026' can mean 1 March or 3 January depending on the server. One exception: SQL Server’s older datetime type can still misread '2026-03-01' under some language settings, so for that type use '20260301' (no dashes), which is always safe.
Free cheat sheet: SQL Server vs MySQL vs PostgreSQL syntax, the same everyday tasks side by side, from paging and NULLs to dates, upsert and new IDs.
Related articles
- SQL WHERE, ORDER BY & LIMIT Explained Simply: the basics of filtering rows.
- HAVING vs WHERE in SQL: filtering monthly totals after grouping.
- Index Seek vs Index Scan: why
YEAR(OrderDate) = 2026is slow. - SQL String Functions Explained Simply: the same idea for text, in SQL Server and MySQL.
- SQL LIKE, IN and BETWEEN Explained Simply: why
BETWEENmisses rows on the last day. - Funnel Analysis in SQL: apply date windows to measure how many users finish each step within a set time.
- Cohort and Retention Analysis in SQL: use DATEDIFF and month truncation to measure how many months after signup each customer returns.
- Month-over-Month and Year-over-Year Comparisons in SQL (With LAG): group dates by month and compare each period with the one before it.