The DATEDIFF function in SQL is used to calculate the difference between two dates, but its behavior varies according to the selected unit (day, month, year). Use DATEDIFF(day, start_date, end_date) to calculate the number of complete 24-hour cycles spanned between two days. Even if the difference is only one second, it will be counted as 1 day as long as the boundary of one day is crossed. For month differences, DATEDIFF(month, date1, date2) counts the number of months spanned, for example, from February to March, regardless of the specific number of days, regardless of whether the corresponding date is true; the same is true for year differences, DATEDIFF(year, date1, date2) only counts the year boundaries crossed, such as from December 31, 2023 to January 1, 2024, as well as 1 year. Therefore, when calculating age or service years, you need to combine functions such as DATEPART to further determine whether the specific month and date have passed; in summary, DATEDIFF is suitable for boundary-based differences calculations. If the precise duration is required or logic involving human habits is required, additional processing is required to ensure accuracy.

If you're working with dates in SQL and need to calculate the difference between two dates, the DATEDIFF function is your go-to tool. It's straightforward but has some nuances depending on what unit you want—days, months, or years.

How DATEDIFF Works for Days

The simplest use of DATEDIFF is to find the number of days between two dates. The syntax typically looks like this:

 DATEDIFF(day, start_date, end_date)

What it does: It counts how many full 24-hour periods fit between the two dates.

? Tip : If your start date is '2024-03-01 23:59:59' and your end date is '2024-03-02 00:00:00', the result will be 1 , even though it's just a one-second difference. That's because it crossed into a new day.

This behavior can trip people up when they expect it to count actual elapsed time in days. If you need more precise differences (like total hours divided by 24), you'd have to do that manually.

Calculating Months Between Two Dates

When using DATEDIFF for months, it checks how many month boundaries are crossed.

Example:

 DATEDIFF(month, '2024-02-28', '2024-03-01') -- returns 1

Even though only three days passed, it still counts as one month because it went from February to March.

?? Important: This doesn't consider whether the exact same day of the month exists. So comparing '2024-01-31' to '2024-02-28' still gives 1 month difference. Be careful if you're trying to determine something like "full months" lived or worked.

If you need more accurate logic (eg, based on actual calendar months including day alignment), you may need to build custom logic or use other functions like DATEADD alongside.

Years Difference – Watch Out for Edge Cases

Using DATEDIFF(year, date1, date2) tells you how many year boundaries fall between the two dates.

Like with months, it doesn't care about exact anniversaries:

 DATEDIFF(year, '2023-12-31', '2024-01-01') -- returns 1

One day apart, yet it shows a difference of one year because it crosses from 2023 to 2024.

So if you're calculating age or tenure, don't rely solely on DATEDIFF(year...) . You might want to compare the full date parts (like month and day) after getting the base year difference.

Here's a quick way to handle age calculation more accurately:

• Get the year difference first
• Then check if the birthday has already occurred this year

That kind of logic usually involves combining DATEDIFF with DATEPART .

Final Thoughts

The DATEDIFF function is powerful, but its behavior depends heavily on the date part you're using. For days, it's mostly intuitive. For months and years, it's boundary-based—not duration-based—which can lead to unexpected results if you're not aware.

• Use DATEDIFF(day...) when you need simple day gaps
• Use DATEDIFF(month...) carefully where crossing into a new month matters
• Use DATEDIFF(year...) with caution, especially for human-related calculations like age

And remember, sometimes you'll need to layer extra logic around it to get exactly what you want.

Basically that's it.

The above is the detailed content of SQL DATEDIFF function for days, months, years. For more information, please follow other related articles on the PHP Chinese website!