Dates Between Two Dates

Calculating the Gap Between Two Dates Without Losing Your Mind

Most people reach for simple subtraction when they need to find what falls between two dates. I tried that exact approach once and completely fumbled a project timeline because I didn't account for leap years crossing century boundaries. The result was off by several days, and my client noticed immediately. What I learned from that mistake shaped how I handle date math now, and I want to share the specifics before you run into the same problems. The concept itself is straightforward enough—Dates Between Two Dates refers to the period separating one timestamp from another, measured in whatever unit makes sense for your situation. But the implementation is where things get messy. Different systems calculate this differently, and they rarely agree with each other.

Why Simple Subtraction Fails

Take two dates, say January 1st and March 1st of a non-leap year. Subtract the day numbers and you might get 60 days. That seems reasonable until you check a calendar. January has 31 days, February has 28, so the actual gap is 59 days when you count the difference in days between them. You're already off by one. Leap years make this worse. The year 2000 was a leap year, but 1900 was not, despite both being divisible by 4. The Gregorian calendar rules are: divisible by 4, except end-of-century years unless also divisible by 400. Any date calculation library worth its salt encodes these rules. Writing your own formula almost guarantees a bug somewhere in the range you're working with.

Using Built-in Functions the Right Way

In SQL Server, the DATEDIFF function is the standard approach: This returns 60, which is correct for counting the number of day boundaries crossed between the two dates. But notice the function counts the difference in the day component, not the actual elapsed time. If you switch the unit to month: The result is 1, even though only 29 days actually passed. The function only cares about whether the month boundary changed, not how many days elapsed between them. This behavior trips people up constantly.

In PostgreSQL, age() gives you a more human-readable interval:

SELECT age('2024-03-01'::date, '2024-01-01'::date);
-- Result: 2 months 0 days

This is more accurate for most business contexts, but it still uses calendar arithmetic rather than pure seconds or milliseconds. If you need exact elapsed time for financial calculations, neither approach works properly. JavaScript Date objects offer yet another path:

const diffTime = Math.abs(new Date('2024-03-01') - new Date('2024-01-01'));
const diffDays = Math.ceil(diffTime / (1000 * 60 * 60 * 24));

This calculates milliseconds first, then converts. The problem is timezone interpretation. If your server runs in UTC but your users are in EST, the midnight boundaries shift and your day count changes depending on when the query runs. I've seen production bugs from exactly this scenario. Here is the specific problem I ran into. A client needed me to calculate service periods for insurance policies that started on February 28th and ended on the same date one year later. They wanted to know whether a policy starting Feb 28, 2023 and ending Feb 28, 2024 covered exactly 365 days or if the leap day in 2024 made it 366. Standard DATEDIFF in SQL Server returned 365, which felt wrong intuitively because 2024 is a leap year. The function was counting day boundaries correctly, but it didn't reflect the actual elapsed time. The policyholder was entitled to an extra day of coverage because 2024 contained February 29th within that span. I had to write a custom calculation that checked whether the end date's year was a leap year and whether February 29th fell between the start and end dates.

SELECT CASE 
    WHEN DATEPART(LEAPYEAR, @EndDate) = 1 
         AND @EndDate >= '2024-02-29' 
         AND @StartDate 
'2024-02-29' 
    THEN DATEDIFF(day, @StartDate, @EndDate) + 1
    ELSE DATEDIFF(day, @StartDate, @EndDate)
END

This workaround added the extra day only when a leap day actually existed within the range. It took me an afternoon to verify all the edge cases, including span boundaries that started after February 29th or ended before it. Counting weekdays or business days between dates introduces a whole separate layer of complexity. Holidays vary by country, state, and sometimes city. The US federal holiday schedule does not match Canada's. Companies often maintain their own holiday calendars for payroll and billing purposes. A common approach uses a calendar table—a dedicated table with a row for every date and flags indicating whether each day is a business day:
SELECT COUNT(*) 
FROM CalendarTable 
WHERE DateColumn BETWEEN @StartDate AND @EndDate 
      AND IsBusinessDay = 1

This is fast and accurate but requires upfront maintenance. If you forget to add 2025 holidays to your calendar table in December 2024, your queries will return wrong results for Q1 2025. I've seen production systems crash on quarter-end reports because the calendar table was stale. An alternative is generating holidays programmatically using known rules, but this breaks for floating holidays like Easter or regional observances. You will always need a lookup table for anything beyond basic Monday-through-Friday counting.
When You Should Not Use Automated Tools

Some teams reach for third-party date libraries for everything. Moment.js was popular for years and has since been deprecated. Day.js and date-fns are better but still add significant bundle weight. For simple date differences, none of these are necessary. JavaScript's built-in Date handles the basics fine if you are careful about timezones. In Python, datetime.date objects are sufficient for most cases. The timedelta class gives you exact day differences:


from datetime import date
delta = date(2024, 3, 1) - date(2024, 1, 1)
print(delta.days)  60

But again, this counts day boundaries, not elapsed calendar days in the way humans expect. The difference between date(2024,1,1) and date(2024,1,2) is 1 day, which is correct for most business logic but wrong if you are trying to count how many full 24-hour periods fit between two moments. For financial or legal date calculations where precision matters, never rely on generic date libraries without explicit validation against your regulatory or business requirements. The definitions of "between" vary by jurisdiction and contract.
Performance Considerations at Scale

If you are calculating date differences across millions of rows, even simple DATEDIFF calls become expensive. SQL Server evaluates the function row by row unless you use computed columns with indexing strategies. One client processed 50 million policy records and the date calculation alone took 45 minutes on a standard query. Adding a persisted computed column with an index brought the runtime down to about 8 minutes for the same data. The tradeoff is storage. Persisted computed columns duplicate data on disk. For a table with billions of rows, this adds meaningful storage cost. Make sure your capacity planning accounts for it before deploying to production.
Summary of Practical Decisions

Choose your approach based on what "between" actually means in your context. Day boundary counting works for most reporting. Exact elapsed time requires millisecond-level arithmetic. Business day counts need a maintained holiday table. Leap year boundaries need explicit checks when the date range crosses February 29th in a leap year. And timezone consistency matters more than you expect until a report comes back wrong on a Monday morning.