Datediff question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Datediff question

Author

Topic

kieran5405
Yak Posting Veteran

96 Posts

Posted - 2009-03-25 : 13:31:13

Hi,

I have the below sql statement which gives me the years (to 2 decimal places) between 2 dates.

However I need to take into account leap years and can see no definite solution in google for my issue.

My 'date from' and 'date to' can range from only a few months to many years so I can't tell if leap years are involved or not in each case. I was looking at dividing by 365.25 but this then wouldn't account for start/end dates occuring between leap years i.e. lasting less than 4 years between the 2 leap years.

Has anyone a definite solution for this.

Thanks,
Kieran

select (convert(decimal(10,2), datediff(dd, [date from],[date to]) / 365.0)) from tbl1

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-25 : 14:06:44

Can you change your specs to just use the number of DAYs instead of the number YEARs to 2 decimal places?
Alternatively, there are some good AGE functions posted here in the Script Library forum that you may be able to borrow from or use directly.

Be One with the Optimizer
TG

kieran5405
Yak Posting Veteran

96 Posts

Posted - 2009-03-25 : 15:22:35

It has to be in years unfortunately.

So do you think I should use an AGE function instead to get it accurate.

Thanks..
Kieran

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-25 : 16:16:59

Now that I think about it those functions probably returns an INT.

Seems like for smaller date ranges - like less than 8 years - where there are only 2 possible leap years to consider, since your result is only good to 1/100th of a year the difference will be beyond the 2 decimal scale (2/365th of a year). right? Do you really need to take leap years into account?

How are you using this value?

If you need to do it right I think you would need to use the following as your denominator:
365.0 + (number_of_leap_years_between_your_dates / ceiling(datediff(day, fromDt, toDt) /365.0))

If you need this precision and agree on the strategy (above) then the last tricky part will be deriving the number of leap years between the dates.

Be One with the Optimizer
TG

kieran5405
Yak Posting Veteran

96 Posts

Posted - 2009-03-25 : 18:24:19

It's to calculate the pension entitlements of staff based on their service i.e. someone may have 20 years service in one result. But someone may have 2 years then a break for 1 year and then 2 months service and so on spread over 10 different results.

It will have to be exact..as noone really wants to work more than necessary!!

I am interested in ur last formula..what does 'ceiling' do or mean..

Thanks for all input..

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-03-25 : 18:58:42

quote:
Originally posted by kieran5405

It's to calculate the pension entitlements of staff based on their service i.e. someone may have 20 years service in one result. But someone may have 2 years then a break for 1 year and then 2 months service and so on spread over 10 different results.

It will have to be exact..as noone really wants to work more than necessary!!

I am interested in ur last formula..what does 'ceiling' do or mean..

Thanks for all input..

I think for someone to help you with that you really need to state the actual business rules for what happens with a break in service.

Usually, it is not nearly as simple as just adding up the total time they worked to get years of service. For example, if you come back after 5 years away, you might not get any credit for your previous time.

CODO ERGO SUM

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-25 : 19:00:57

CEILING is a built in function that returns the smallest integer greater than, or equal to, the specified numeric expression. So anything equal to or less than 1 year would return 1, less than or equal to 2 years would return 2, etc.

Be sure to spend a lot of time with Books Online

http://msdn.microsoft.com/en-us/library/ms189818(SQL.90).aspx

Do you need help coming up with a way to count leap years?

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-25 : 19:06:05

Ah! you're lucky to have MVJ on the case. He wrote some of those AGE functions I referred to (the good ones anyway

)

Be One with the Optimizer
TG

kieran5405
Yak Posting Veteran

96 Posts

Posted - 2009-03-25 : 19:33:29

Cheers for feedback...

MVJ..I have already a way to identify breaks in service and so have removed them from the figures that are totaled.

TG If I could get a way to count leap years between the start and end date then I could test the function and hopefully that would be it..any ideas..

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-03-26 : 00:08:27

What are the actual units of time that you need to calculate? Years, months, days, etc.?

What is your exact definition of a year, or whatever time period you are trying to calculate? For example, from a particular day one year to a same day the next year might be considered one year.

Instead of getting hung up on the code you have, try explaining the problem better, and we may be able to give you a better solution.

CODO ERGO SUM

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-26 : 00:13:39

This seems to do the trick. Set the FROM and TO dates (@f, @t). Result is number of years to 2 decimals between the dates.


declare @f datetime
       ,@t datetime

select @f = '4/10/1999'
       ,@t = '3/27/2009'

select convert(decimal(5,2), 

       --number of days in range
       datediff(day, @f, @t) 

       / (

       --365 + (num of leap years / num years in range)
       365.0 + (count(*) / ceiling(datediff(day, @f, @t) /365.0))

       ))

----------------------------------------------------------
--This returns all leap years between 1928 and 2096

--select dateadd(day, number*1461, '1928-02-29')  --there are 1461 days between leap years
from   master..spt_values                         --this table is just a table of sequencial integers starting from 0
where  type = 'P'
and number < 43

----------------------------------------------------------
--Limit the leap years to the ones in our date range
and    dateadd(day, number*1461, '1928-02-29') >= @f
and    dateadd(day, number*1461, '1928-02-29') <= @t

Be One with the Optimizer
TG

kieran5405
Yak Posting Veteran

96 Posts

Posted - 2009-03-26 : 06:34:06

Sorry MVJ if my question was unclear...essentially what im trying to do is get the exact years (to 2 decimal places) between 2 dates.
These dates may span up to 40 years or may span only a few months. These time spans relate to the years worked in our company (need 40 years to retire). People may have many time spans as they may have left and returned, taken career breaks, maternity etc... over the years. The time span is then totaled for each person to give their job service in years (to 2 decimal places)...which in turn lets them know how long they have left before they are granted freedom...just like prision!!
I do appreciate ur time though.

TG...I have been testing ur formula here and it seems to work great. I have one question about the last part of the function (see below) - should the 2nd '1928-02-29' be '2096-02-29'.

--Limit the leap years to the ones in our date range
and dateadd(day, number*1461, '1928-02-29') >= @f
and dateadd(day, number*1461, '1928-02-29') <= @t

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-26 : 08:07:57

>>should the 2nd '1928-02-29' be '2096-02-29'

no. If you run just the highlighted portion (below) you will see that the particular dateadd expression just returns all the leap years. The WHERE clause portion you asked about just uses that "leap year" expression to limit the leap year values to the ones within your date range.


--select dateadd(day, number*1461, '1928-02-29')  --there are 1461 days between leap years
from   master..spt_values                         --this table is just a table of sequencial integers starting from 0
where  type = 'P'
and number < 43

Be One with the Optimizer
TG

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-03-26 : 08:09:10

The problem with years to two decimal points is that there really isn't a definition of years as a decimal number.

Since the year varies in length, there is no definition of a length of time of a year, except over a specific time range of From date and To date. Months have the same problem. Any assignment of decimal places to either will be arbitrary, with the same number representing two different time spans.

The largest unit of time that is fixed in length is days.

That is why I sugessted a reference to your business rules, to be able to determine the actual definition of what people are given credit for any span of time. For example, if people are given credit at the level of months worked with no credit for partial months, then it is easy to count the number of months in each time span and add them up.

CODO ERGO SUM

kieran5405
Yak Posting Veteran

96 Posts

Posted - 2009-03-26 : 15:11:49

cheers for help guys...

that seems like the solution TG...ive been manually testing it and it seems like it'll work great!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-26 : 16:47:19

Compare this code with TG's and set @f to November 5, 1952 and set @t to April 7, 2209.

SELECT	CAST(SUM(
	CASE
		WHEN y = 0 THEN 1.0E * (t - f) / d
		WHEN Number = 0 THEN 1.0E * (d - f) / d
		WHEN Number = y THEN 1.0E * t / d
		ELSE 1.0E
	END) AS DECIMAL(10, 2))
FROM	(
		SELECT	Number,
			DATEDIFF(YEAR, @f, @t) AS y,
			CASE ISDATE(10000 * Number + 10000 * DATEPART(YEAR, @f) + 229)
				WHEN 0 THEN 365
				ELSE 366
			END AS d,
			DATEPART(DAYOFYEAR, @f) AS f,
			DATEPART(DAYOFYEAR, @t) AS t
		FROM	master..spt_values
		WHERE	Type = 'P'
			AND Number <= DATEDIFF(YEAR, @f, @t)
	) AS d

TG's suggestion will return 256.49 years.
My suggestion will return 256.42 years, and so will this simple rewrite return

SELECT	CONVERT(DECIMAL(10, 2), DATEDIFF(DAY, @f, @t) / 365.2425E)

E 12°55'05.63"
N 56°04'39.26"

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-26 : 17:02:49

as-is, my solution only works from 1928 to 2096

EDIT:
we get the same answer for:
select @f = '11/05/1952'
,@t = '04/07/2096'

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-26 : 17:14:53

However we get different answers for this (nearly 8 year) period which only has 1 leap year:

select @f = '03/01/2004', @t = '02/28/2012'

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-26 : 17:17:05

I still think the best thing is to just use the retirement threshold as a total number of days as opposed to trying to normalize to a "standardized" year.

Be One with the Optimizer
TG

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-26 : 17:27:26

You are right. For the first year, the days to calculate are included @f date.

WHEN Number = 0 THEN 1.0E * (d - f + 1) / d

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources