SQL: month to month computation

Question

I want to compute for month difference of 2 dates which will return a float value.

example:

date1='4/23/2008' date2='12/31/2008'

that will be 7.y months. I want to find the y value. can someone give me the formula to make this in sql codes? tnx..

Answer 1

There's no standard sql to handle this universally. It depends on what database you're using. Basically, you need to convert the two date strings to the date format your database engine uses internally and subtract the two dates, what the result of the subtraction means will, again, depend. In Oracle, you'd use to_date() for the conversion and the result of the subtraction will be a float representing the time in number of days.

In short, search your db's manual for a section about date handling or data types.

Answer 2

An approximately result for Sql Server;

select cast(datediff(dd, date1, date2) as float) / 30

Answer 3

Because months are of different lengths, for the sake of simplicity I'm going to assume 30 days in a month. With one-digit accuracy this won't make much difference, but do not try to calculate new dates based on the resulting value, as they will not be 100% accurate.

(I'll try and find a way to format that more nicely...)

DECLARE @d1 DATETIME
DECLARE @d2 DATETIME

SET @d1 = '2008-04-23'
SET @d2 = '2008-12-31'

SELECT CONVERT(FLOAT, DATEDIFF(mm, @d1, @d2)) + ROUND(CONVERT(FLOAT, DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm, @d1, @d2), @d1), @d2) % 30) / 30, 1)

This yields:

8.3

The formula takes into account that between '2008-04-13'and '2008-11-05', DATEDIFF(mm ...) returns 7, but really it is not seven months in between, but less. In this example it returns:

6.7