DATETRUNC, EOMONTH & Choosing a Date Function

To report "runs per month", every match date in March must become the same value. You could extract YEAR and MONTH separately, but SQL Server has a cleaner tool: DATETRUNC. And for deadlines like "the auction closes at the end of the month" there is EOMONTH.

Definition

DATETRUNC(part, date) returns the date truncated to the given part: it keeps that part and everything bigger, and resets every smaller part to its starting value (day → 1, time → 00:00:00). The result is still a date/time value, not a number or text.

EOMONTH(date [, months_to_add]) returns the last day of the month (End Of MONTH) that contains the date, as a DATE. The optional second argument first moves forward (or back, if negative) by that many months.

"Truncate" means "cut off". DATETRUNC doesn't round — 2025-08-20 truncated to month becomes 2025-08-01, never 2025-09-01. DATETRUNC was added in SQL Server 2022; older versions don't have it.

Syntax

DATETRUNC(part, date)                 -- part: year, quarter, month, week, day, hour, minute, second
EOMONTH(start_date [, months_to_add])

Example — DATETRUNC on one timestamp

SELECT
    DATETRUNC(year,  '2025-08-20 18:55:45') AS to_year,
    DATETRUNC(month, '2025-08-20 18:55:45') AS to_month,
    DATETRUNC(day,   '2025-08-20 18:55:45') AS to_day,
    DATETRUNC(hour,  '2025-08-20 18:55:45') AS to_hour;

How it works: month keeps 2025-08 and zeroes the day and time; hour keeps everything up to 18 and zeroes minutes and seconds. (SQL Server shows the fractional seconds too, e.g. 2025-08-01 00:00:00.0000000.)

Example — group innings by month

SELECT
    DATETRUNC(month, match_date) AS match_month,
    COUNT(*)                     AS innings_played,
    SUM(runs)                    AS total_runs
FROM innings
GROUP BY DATETRUNC(month, match_date)
ORDER BY match_month;

How it works: every March date becomes 2025-03-01, so all March innings fall into one group. In SQL Server, because match_date is a DATE column, the result stays a DATE and shows as 2025-03-01 (the playground always shows the time part). The NULL innings of MS Dhoni counts in COUNT(*) but is ignored by SUM.

Example — EOMONTH

SELECT
    EOMONTH('2025-08-20') AS aug_end,
    EOMONTH('2025-02-10') AS feb_2025,
    EOMONTH('2024-02-10') AS feb_2024_leap;

EOMONTH understands leap years (2024 has 29 February). In SQL Server the second argument shifts the month first: EOMONTH('2025-08-20', 1) = 2025-09-30. (The browser playground supports only the one-argument form.)

SELECT
    player,
    match_date,
    EOMONTH(match_date)                          AS month_end,
    DATEDIFF(day, match_date, EOMONTH(match_date)) AS days_left
FROM innings
WHERE player = 'Jos Buttler';

How to choose the right date function

Same value 2025-08-20 18:55:45 — three different outputs:

Common mistakes

• ❌ Expecting DATETRUNC to round up (2025-08-20 → 2025-09-01). ✅ It always cuts down to the start.
• ❌ DATETRUNC(weekday, d) → error; weekday is not a valid DATETRUNC part.
• ❌ Grouping by MONTH(match_date) alone mixes March 2024 with March 2025. ✅ DATETRUNC(month, match_date) keeps the year.
• ❌ Hard-coding month ends like '2025-02-30'. ✅ Let EOMONTH work it out.

SQL Server vs MySQL / PostgreSQL

Interview tip

Q: Find the first day of the current month in SQL Server. DATETRUNC(month, GETDATE()) on SQL Server 2022+. On older versions: DATEADD(day, 1, EOMONTH(GETDATE(), -1)) — the day after the previous month's end.