Working with dates in SQL Server is something almost every developer runs into. You might need to figure out how many days are left in the month, get the first day of the quarter, calculate someone’s age, or add a few weeks to a deadline. Fortunately, SQL Server gives us some simple but powerful tools to help with these kinds of tasks.

In this article, we’ll walk through three of the most useful date functions: EOMONTH, DATEDIFF, and DATEADD. We’ll look at what each one does, how it works, and how you can use them in your own queries. We'll keep things clear and practical, with real examples you can follow step-by-step.

Getting the Last Day of the Month with EOMONTH

Let’s say you’re building a report that needs to show the last day of each month. Instead of figuring that out manually or writing extra logic, you can just use EOMONTH, which stands for “End of Month.”

SELECT EOMONTH('2025-04-15') AS EndOfMonth;

In this example, we’re passing in April 15th. The function doesn’t care about the exact day — it just looks at the month and gives you the last day of it. So the result here is April 30th.

If you want the last day of next month instead, you can give the function a second argument that shifts it forward one month.

SELECT EOMONTH('2025-04-15', 1) AS EndOfNextMonth;

Now the function takes the same starting point, April 15th, then moves one month forward to May. It then gives you the last day of May, which is the 31st.

You can also move backward in time by giving it a negative number. This next example returns the last day of the previous month.

SELECT EOMONTH('2025-04-15', -1) AS EndOfPreviousMonth;

Here, the function steps back to March and returns March 31st. This is especially helpful when you’re building reports that need to look at current and previous months side by side.

Measuring Time Gaps with DATEDIFF

If you want to know how far apart two dates are, DATEDIFF makes that easy. You tell it the unit of time you care about, like days or years, and then give it the two dates.

SELECT DATEDIFF(day, '2025-04-01', '2025-04-18') AS DaysPassed;

In this query, we’re asking SQL Server to count how many full days are between April 1st and April 18th. It looks at the two dates and returns 17, since that’s the number of day boundaries between them.

You can use different units too. The next example uses year instead of day.

SELECT DATEDIFF(year, '1995-07-01', '2025-07-01') AS Years;

This one counts how many times the year flipped between the two dates. Since both dates fall on July 1st, it gives a clean result of 30. But keep in mind that DATEDIFF(year, …) doesn't check whether the birthday already happened this year — it just compares the year numbers. If the second date was June 30th, it would still return 30 even though the person hasn’t turned 30 yet. That’s something to be aware of if you're using this to calculate age.

Shifting Dates with DATEADD

Sometimes, instead of comparing two dates, you want to move a date forward or backward in time. That’s what DATEADD is for.

SELECT DATEADD(day, 7, '2025-04-01') AS NewDeadline;

This adds 7 days to April 1st. SQL starts from the date you provide, counts forward 7 full days, and returns April 8th. This is great for calculating deadlines, renewal periods, or follow-up dates.

You can also subtract time by giving it a negative number.

SELECT DATEADD(month, -3, GETDATE()) AS ThreeMonthsAgo;

Here we’re starting from today, and subtracting three months. GETDATE() gives us the current date, and DATEADD shifts that back by three full months. This kind of pattern is helpful when you want to pull data from a rolling timeframe, like the last 90 days.

Real-World Examples

Let’s put everything together and walk through a few practical examples.

Here’s how you can get the first day of the current month. There’s no built-in function that gives you this directly, but you can work around that by taking the end of last month and adding one day to it.

SELECT DATEADD(day, 1, EOMONTH(GETDATE(), -1)) AS FirstDay;

This works by calling EOMONTH with a -1, which gives you the last day of the previous month. Adding one day to that gets you the first day of the current month. So if today is April 18th, the result will be April 1st.

Now let’s look at a more accurate way to calculate someone’s age. DATEDIFF(year, ...) gets you close, but doesn’t account for whether the birthday has already happened this year. To fix that, we can use a CASE statement and compare the current date to their birthday for this year.

DECLARE @Birthdate DATE = '1995-07-01';
SELECT 
CASE 
WHEN DATEADD(year, DATEDIFF(year, @Birthdate, GETDATE()), @Birthdate) > GETDATE()
THEN DATEDIFF(year, @Birthdate, GETDATE()) - 1
ELSE DATEDIFF(year, @Birthdate, GETDATE())
END AS ExactAge;

Here’s what’s happening: we first use DATEDIFF(year, @Birthdate, GETDATE()) to get the rough age. Then we take that number and add it back to the original birthdate to simulate the person’s birthday this year. If that simulated birthday hasn’t happened yet, we subtract one. If it has, we keep the age as-is.

You can also use DATEDIFF to figure out how many days are left in the month.

SELECT DATEDIFF(day, GETDATE(), EOMONTH(GETDATE())) AS DaysLeft;

In this case, GETDATE() gives you today’s date, and EOMONTH(GETDATE()) gives you the last day of the current month. SQL then counts how many full days are between those two points.

 A Practical Example: Building a Rolling 30-Day Sales Report

Let’s say you’re building a dashboard that shows sales from the last 30 days. You don’t want to hardcode dates into your query — you want it to always use the current date as the starting point.

SELECT OrderID, OrderDate, TotalAmount
FROM Sales
WHERE OrderDate >= DATEADD(day, -30, GETDATE());

This query takes today’s date and subtracts 30 days from it. The result is used to filter the OrderDate column, so only sales that happened in the last 30 days are shown. Because it uses GETDATE(), the result updates automatically every day.

You can also build a version that shows just the sales from the current month.

SELECT OrderID, OrderDate, TotalAmount
FROM Sales
WHERE OrderDate >= DATEADD(day, 1, EOMONTH(GETDATE(), -1))
AND OrderDate <= EOMONTH(GETDATE());

This one starts by getting the first day of the month — again using the trick of getting the end of the previous month and adding a day. It also uses EOMONTH(GETDATE()) to get the last day of the current month. The WHERE clause filters for any order that happened between those two dates. This pattern works for monthly reports, invoices, or any task that needs clean start and end dates.

Final Thoughts

With just three functions — EOMONTH, DATEDIFF, and DATEADD — you can handle most of the date logic you’ll run into in SQL Server. Whether you’re building reports, calculating ages, or setting up time-based filters, these functions let you write clean, reliable queries that update automatically as time passes.

Once you get familiar with how they work, you can start combining them in different ways to solve more complex problems. It’s a good skill to have in your toolkit, and it’ll save you time on anything date-related down the road.

Let me know if you’ve used these functions in interesting ways — or if you run into a case they didn’t quite cover. There’s always more to learn.