March 22, 2011 at 2:22 am
Hi everyone,
We have developed an HR system and I need to determine the months of service of employees, please follow this sample:
1) The months service at 30 June 2006 for an employee appointed on 1 Jan 2006 is:
SELECT DateDiff(month, '01/01/2006', '06/30/2006') -> The result is 5, which is correct
2) The months service at 1 July 2006 for an employee appointed on 1 Jan 2006 is:
SELECT DateDiff(month, '01/01/2006', '07/01/2006') -> The result is 6, which is correct, because the 1st is the anniversary day.
3) BUT , THIS IS ILLOGICAL:
=====================
If I take the dates in 1) and add 1 day on both sides in other words the months service at 1 July 2006 for an employee appointed on 2 Jan 2006 is:
SELECT DateDiff(month, '01/02/2006', '07/01/2006') -> The result is 6 !!! It should still be 5, because the anniversary day is only on the 2nd.
Can someone please tell me why does this function behave like that and is there any alternative function that one can call to return MONTHS of service correctly ?
If I make the "datepart" as days, then both the above is correct.
Thanks in advance.
March 22, 2011 at 2:46 am
This is expected behaviour..
Returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate
http://msdn.microsoft.com/en-us/library/ms189794.aspx
You will have to account for the number of days through the month yourself , similar to this
http://sqlblogcasts.com/blogs/sqlandthelike/archive/2009/12/08/age-calculation-with-sql-server.aspx
March 22, 2011 at 3:13 am
Thanks a lot Dave for the speedy response and links.
I am very curious as to WHY this behaviour is by design, because it does not make sense.
March 22, 2011 at 3:22 am
Hmm , Why's are always tough questions to answer , you would have to go back to the very early sybase releases to find the answer. Though i doubt there would be any available documented discussion on the subject 😉
March 22, 2011 at 3:46 am
Thanks Dave, but the same behaviour also applies to the DateDiff function in Microsoft Visual Studio (VB.NET, C#, VB6), so this seems to be a Microsoft decision and definition.
To recap, below returns 5 months and 6 months, but, the actual duration between the dates are exactly the same, only difference is that the date range shifted by 1 day :
SELECT DateDiff(month, '01/01/2006', '06/30/2006')
SELECT DateDiff(month, '01/02/2006', '07/01/2006')
What I really don't understand is that there is no actual TIME DIFFERENCE between the two dates, they equate to the exact same amount of time, 180 days if "day" is used as the datepart. There is also no issue of rounding as we are purely subtracting two “numbers” represented as dates on a linear timeline.
Let’s reverse the equation : The difference in days is 180 in both cases, which is correct, and if we assume there are 30 days per month, then 180 / 30 = 6 months, which is correct, BUT 5 months x 30 = 150 days, so what happened to the other 30 days ???? It does not make sense, you get the same difference if using seconds or days as datepart, but when you use months, then 30 days have disappeared... huh ... ?
Is there any other guru that can shed some light on the WHY part ?
March 22, 2011 at 1:15 pm
You have to stop thinking of it in terms of elapsed time, as Dave said. That's not what the calculation is intending to figure out. You can go to lower increments with no difference between those two (days, hours, minutes, seconds all work), but you can't always go up (years doesn't necessarily work, depending on the boundaries).
SELECT DATEDIFF(yyyy,'01/01/2006','06/30/2006')
SELECT DATEDIFF(yyyy,'12/31/2005','06/29/2006')
That'd be even worse, same elapsed time with one calendar day difference results in the difference of a year.
Or the extreme example:
SELECT DATEDIFF(yyyy,'12/31/2005 23:59:59.997', '01/01/2006 00:00:00.000')
I can't speak as to why they made the choice to track boundaries originally rather than using an increment, but I think doing anything else would be just as confusing, if not more so.
What would you use for a month? 30 Days? What about months that have 31 etc? That'd break a lot of date math.
If we just decided that 30 days was a month, you'd no longer get '07/01/2006' by adding 6 months ( converted to 180 days) to 01/01/2006. You'd get 06/30/2006. Doing the same addition for '01/01/2008' you'd get 06/29/2008.
So, at the end of the day, if you're tracking 'months' of employment as a calculation based on 'days' rather than actual calendar months, then use days and do the division.
March 22, 2011 at 1:33 pm
The reason it has to work on "boundaries" instead of day multiples or something like that is simple to demonstrate:
How many months are there between Feb 5 and Mar 5, and is it the same number of months as between Jan 5 and Feb 5?
One of those is 31 days, the other is either 28 or 29 days, depending on the year. Are they both "1 month"? Logically, it would seem so.
How about how many months are there between Jan 5 and Mar 4? Is it two on Leap Year and one the rest of the time, or is it always one, or is it "close enough to two"? What about if those two dates are three years apart?
Different businesses will have different rules for that kind of thing. Heck, people born on Feb 29 have different LEGAL rules about when their birthdays are on non-Leap Years depending on where they live, etc. (If you think that's trivial, just remember that it can affect things like voting age, drinking age, statuatory rape laws, tax laws, criminal penalty determination, even the number of Reps a state gets in the US Congress. All of those are things that have tremendous importance to the right people.)
How could Microsoft, or anyone else, comply with all possible rules on "how many X units of time are there in this date range"? They really can't, so they don't even try.
You should see the problems that come up when you even do something as simple as trying to determine a person's age mathematically, the moment you run into rounding errors, or floating point issues, or "Leapling" rules, etc.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 22, 2011 at 1:38 pm
anton.marais (3/22/2011)
Thanks Dave, but the same behaviour also applies to the DateDiff function in Microsoft Visual Studio (VB.NET, C#, VB6), so this seems to be a Microsoft decision and definition.To recap, below returns 5 months and 6 months, but, the actual duration between the dates are exactly the same, only difference is that the date range shifted by 1 day :
SELECT DateDiff(month, '01/01/2006', '06/30/2006')
SELECT DateDiff(month, '01/02/2006', '07/01/2006')
What I really don't understand is that there is no actual TIME DIFFERENCE between the two dates, they equate to the exact same amount of time, 180 days if "day" is used as the datepart. There is also no issue of rounding as we are purely subtracting two “numbers” represented as dates on a linear timeline.
Let’s reverse the equation : The difference in days is 180 in both cases, which is correct, and if we assume there are 30 days per month, then 180 / 30 = 6 months, which is correct, BUT 5 months x 30 = 150 days, so what happened to the other 30 days ???? It does not make sense, you get the same difference if using seconds or days as datepart, but when you use months, then 30 days have disappeared... huh ... ?
Is there any other guru that can shed some light on the WHY part ?
Instead of worrying about all of that, you should provide an exact definition of exactly what you would consider "months of service" and probably someone can help you with that.
Provide enough clear examples to illustrate what you are after:
start date, current date, and months of service
Also, you might try looking at the fnMonthsApart function on this link to see if it does what you want:
March 22, 2011 at 1:58 pm
When you're asking for the difference in months, SQL Server basically truncates the days of the dates you compare:
For example:
SELECT DATEDIFF(mm,'20101231','20110101')
SELECT DATEDIFF(mm,'20101201','20110131')
Both queries return the same value (1), since the difference between December 2010 and Januar 2011 is one month.
The same applies for all other dateparts, e.g. years:
SELECT DATEDIFF(yy,'20101231','20110101')
SELECT DATEDIFF(yy,'20100101','20111231')
In the first query, there really is a difference of one day, whereas the second query covers two years.
March 23, 2011 at 2:12 am
Thanks everyone for your contributions, I have certainly learned a lot through this discussion, and where I misinterpreted the function was that I saw it as ELAPSED TIME.
In terms of the "boundary" definition of the function: I think it is safe to say that “datepart” defines the smallest unit of time, and any other time units will be ignored. If for example “years” is used as datepart, then any time unit smaller than years eg. months, weeks, days, hours, minutes, seconds will be ignored and the function will only look at the specified “years” portion in the attributes. The same applies when “months” is used, then days, hours, minutes, seconds will be ignored and the function will only look at the specified “months” portion in the attributes.
That is why SELECT DateDiff(month, '01/02/2006', '07/01/2006') returns 6, even though the ELAPSED time according to a calendar is 5 months. If my above laymens definition is true, then the function only looks at month 1 vs. month 7 and gets a difference of 7 - 1 = 6.
If anyone of you is connected to Microsoft, it would be good to clarify the definition of "boundary" in laymens terms in the documentation, instead of just saying : "Returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate."
March 24, 2011 at 6:38 am
Actually, you can suggest that kind of thing on connect.microsoft.com.
It comes up as a point of confusion often enough that clarified documentation would be a good idea. If you do suggest it, let us know here (provide a link), and I'll go vote in favor of it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply