Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server - How to Calculate the number of fractional months between two dates (UDF)

Posted on 2009-07-14
9
Medium Priority
?
1,771 Views
Last Modified: 2012-06-21
I want to create a UDF that  Calculates the number of fractional months between two dates.
E.g. from 01/01/2008 to 14/02/2008 = 1.542 Months.

I have tried using  Datediff function but that has some issues.

First issue :
It only returns whole months.

second Issue :
The DateDiff function will return the number of month boundaries between 2 dates.
so, 01/01/2008 to 31/01/2008 will return 0. This i have fixed by adding 1 to end date.
but since I cannot get fractions 01/01/2008 to 15/01/2008 returns 0 instead of around 0.5.

Third Issue :
The function should handle the dates even if they span over multiple years e.g 02/03/2006 to 02/03/2008 = 24 months

Kindly give some code to do this. Thanks.  More info:
The function will be used to calculate the number of leave someone has earned at the of 1.75 days per month. so if someone starts employment on the (dd/mm/yyyy) 01/01/2008, on the 01/02/2008 he would have 1 month x 1.75 days of leave which is  = 1.75
similarly if sumone starts on the 15/01/2008, on the 01/02/2008 he would have earned 0.5 of a month x 1.75 = 0.875 days. So thats why I require fractional months.
0
Comment
Question by:venussc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 24856681
did you try using datediff with days and the divide the days by 30 ?
0
 

Author Comment

by:venussc
ID: 24856700
Tried that. The problem with that is to get exact number of days. some months has 28/29/30/ or 31 days. The logic being from 15/01/2008 to 15/02/2008 = 1 month and have 31 days but 15/02/2008 to 15/03/2008 = 1 moth but has 28 days
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 24856729
well, that is pretty much your only option, since the datediff function will only return whole months...
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:venussc
ID: 24856881
Awaiting more feedback please
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 24857048
Hello venussc,

DATEDIFF when used with a monthly interval does *not* return the number of whole months between two
datetimes.  Rather, it returns the number of *month boundaries* between the two datetimes.

For example:

SELECT DATEDIFF(month, '2009-01-31', '2009-02-01')            --- >      1

SELECT DATEDIFF(month, '2009-01-31', '2009-03-01')            --- >      2

Those results are certainly not "whole month" :)

Regards,

Patrick
0
 
LVL 26

Accepted Solution

by:
tigin44 earned 2000 total points
ID: 24857199
you can configure the code below to give you the fractional month. Hope this helps.
DECLARE @startDate	datetime,
		@endDate	datetime;
SET	@startDate = '01.01.2008';
SET @endDate = '02.14.2008';
 
DECLARE @wholePart	float;
DECLARE @fraction	float;
 
SELECT @wholePart = DATEDIFF(MONTH, @startDate, @endDate) * 1.0
SELECT @fraction = DATEDIFF(DAY, DATEADD(MONTH, @wholePart, @startDate), @endDate) *1.0 / CASE WHEN MONTH(DATEADD(MONTH, -1, @endDate)) IN (1, 3, 5, 7, 8, 10, 12) THEN 31.0
																							   WHEN MONTH(DATEADD(MONTH, -1, @endDate)) IN (4, 6, 9, 11) THEN 30.0
																							   WHEN (MONTH(DATEADD(MONTH, -1, @endDate)) = 2) AND (YEAR(@endDate) % 4 = 0) THEN 29.0
																							   ELSE 28.0 END
 
SELECT @wholePart + @fraction

Open in new window

0
 

Author Closing Comment

by:venussc
ID: 31603604
Thanks Guru tigin44. I think with this method I can adapt to whatever exact condition I will need.
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 24858747
tigin44,

Brilliant answer!  I did a little testing, and found the following quirks:

1) In the unlikely event that dates from 1900 or 2100 are fed in, the results may be wrong by a tiny amount:
years divisible by 100 but *not* divisible by 400 are standard years, not leap years.

2) Depending on how the Asker defines "whole months", there may be an issue with a start date of 31 Jan
and end date of 1 Mar (for any year).  For leap years the answer your SQL returns is 0.9655, and for
standard years it's 0.9286.  That may be what the Asker wants, but then again it could be that the Asker
would want to see a number slightly greater than 1 (as all of February is encompassed in the time period).

Regards,

Patrick
0
 

Author Comment

by:venussc
ID: 24859087
Nice Spotting matthewspatrick, I had tested with quite a few dates but not what you used. I can indeed replicate your results of 0.9655.
I indeed expect to get 1.0322 or something like that as the result for your above dates. matthewspatrick do you see any way to adjust the function to do that.

Thanks again for your observation.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

664 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question