10/10/2012

[SQL] Difference in months/years between two dates

In Oracle SQL, you can easily calculate the difference in months between two dates by using the MONTHS_BETWEEN function:

SELECT MONTHS_BETWEEN (date1, date2)

Note that if the difference is X months and Y days, you'll get a float number. You can truncate it with FLOOR or CEIL or ROUND(float_number, 0) depending on your needs.

Should you be looking for the difference in years, you can use the EXTRACT function:

SELECT EXTRACT(YEAR FROM date1) - EXTRACT(YEAR FROM date2)

This will obviously always return an integer.

If you're using Microsoft SQL Server, you may find DATEDIFF useful instead.


No comments:

Post a Comment

With great power comes great responsibility.

Da grandi poteri derivano grandi responsabilità.