I have a simple SQL query shown below. If you look at scenario 1, the difference between dates in 1 month and that is correct (because Oct 5th - Sep 5th).
However, when you look at scenario 2, the difference between dates in still showing as 1. But it should be 0 (because Oct 4th - Sep 5th is still not 1 month).
Can we make sure the scenario 2 shows as 0?
Scenario 1
select
datediff(month, convert(datetime, '05-09-24', 5), convert(datetime, '05-10-24', 5))
1
Scenario 2
select
datediff(month, convert(datetime, '05-09-24', 5), convert(datetime, '04-10-24', 5))
1
If you only care about dates (and not times) you can use the expression:
DATEDIFF(month, date1, date2)
- CASE WHEN DAY(date1) > DAY(date2) THEN 1 ELSE 0 END
If you care about time and consider the difference between 2024-07-15 16:44:30 to 2024-08-15 16:44:29 to be less than one month (by one second), you can use the following:
DATEDIFF(month, datetime1, datetime2)
- CASE WHEN DAY(datetime1) > DAY(datetime2)
OR (
DAY(datetime1) = DAY(datetime2)
AND CAST(datetime1 AS TIME) > CAST(datetime2 AS TIME
)
THEN 1 ELSE 0 END
or alternately:
DATEDIFF(month, datetime1, datetime2)
- CASE WHEN DATEADD(month, DATEDIFF(month, datetime1, datetime2), datetime1)
> datetime2
THEN 1 ELSE 0 END
Each will adjust the month difference down by 1 if the first date/time is later in the month than the second date/time.
Note that for near-end-of-month start date/times where the ending months are shorter, the month will not be credited until the date rolls over to the 1st of the following month. For example:
2024-01-31 16:45 to 2024-02-29 23:59 is considered 0 months.2024-01-31 16:45 to 2024-03-01 00:00 is considered 1 month.Sample results:
| datetime1 | datetime2 | comment | Diff Raw |
Diff 1 |
Diff 2 |
|---|---|---|---|---|---|
| 2024-09-05 00:00 | 2024-10-05 00:00 | 1 month | 1 | 1 | 1 |
| 2024-09-05 00:00 | 2024-10-04 00:00 | 1 day short of 1 month = 0 months | 1 | 0 | 0 |
| 2024-09-05 00:00 | 2024-12-04 00:00 | 1 day short of 3 month = 2 months | 3 | 2 | 2 |
| 2024-09-05 00:00 | 2024-12-05 00:00 | 3 months | 3 | 3 | 3 |
| 2024-09-05 00:00 | 2024-12-31 00:00 | 3 months, 26 days | 3 | 3 | 3 |
| 2024-09-05 00:00 | 2025-01-01 00:00 | 3 months, 27 days | 4 | 3 | 3 |
| 2023-12-31 23:59 | 2024-01-01 00:00 | 1 minute, crossing month boundary | 1 | 0 | 0 |
| 2024-07-15 16:45 | 2024-08-15 16:44 | 1 minute short of 1 month = 0 months | 1 | 1 | 0 |
| 2024-07-15 16:45 | 2024-08-15 16:45 | exactly 1 month | 1 | 1 | 1 |
| 2024-01-31 16:45 | 2024-02-29 23:59 | Not yet 1 month at end of short month | 1 | 0 | 0 |
| 2024-01-31 16:45 | 2024-03-01 00:00 | 1 month after short month rollover to next | 2 | 1 | 1 |
| 2024-02-29 16:45 | 2024-03-30 00:00 | 1 month short to long | 1 | 1 | 1 |
Where:
DiffRaw is the original DATEDIFF() calculation.Diff1 is the day-only adjustment calculation from above.Diff2 is the day + time adjustment calculation from above.See this db<>fiddle for a demo.