February 16, 2015 at 11:48 pm
Hi,
I am not an expert in SQL , but i been trying to get the difference of two dates in years.days format like
1 year 230 days --> 1.230
1 year 23 days --> 1.023
1 year 2 days --> 1.002
Suppose if the Dates are like @startDate = '20090128' and @endDate = '20150217'
my out put should be like 6.020 ( 6 Years and 20 Days )
I have tried like anything , but i am not able to get the output
Please help me out guys !!!!
February 17, 2015 at 12:40 am
You can start with this calculation:
DECLARE @D1 DateTime = '20090128',
@D2 DateTime = '20150217';
SELECTCAST (
DATEPART(YEAR, DATEADD(Day, DATEDIFF(Day, @D1, @D2), 0)) - 1900.0 +-- # years
DATEPART(dy, DATEADD(Day, DATEDIFF(Day, @D1, @D2), -1 )) / 1000.0-- Datepart
as decimal(5,3))
Louis.
February 17, 2015 at 12:51 am
Guitar_player (2/16/2015)
Hi,I am not an expert in SQL , but i been trying to get the difference of two dates in years.days format like
1 year 230 days --> 1.230
1 year 23 days --> 1.023
1 year 2 days --> 1.002
Suppose if the Dates are like @startDate = '20090128' and @endDate = '20150217'
my out put should be like 6.020 ( 6 Years and 20 Days )
I have tried like anything , but i am not able to get the output
Please help me out guys !!!!
Have a look at this:
declare @D1 datetime = '20090128'
,@D2 datetime = '20150217';
declare @Yrs int
,@dys int;
set @Yrs = datediff(year, @D1, @D2);
set @dys = datediff(day, dateadd(year, @Yrs, @D1), @D2)
select Years = @Yrs
,Days = @dys
,Fraction = @Yrs + @dys / 1000.0;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 17, 2015 at 1:12 am
DECLARE @D1 DateTime = '20090128',
@D2 DateTime = '20150217';
SELECTCAST (
DATEPART(YEAR, DATEADD(Day, DATEDIFF(Day, @D1, @D2), 0)) - 1900.0 +-- # years
DATEPART(dy, DATEADD(Day, DATEDIFF(Day, @D1, @D2), -1 )) / 1000.0-- Datepart
as decimal(5,3))
Hi Louis ,
Thanks for the reply but this wont give me perfect output when a leap year comes up ..as pls check this
DECLARE @D1 DateTime = '20110228',
@D2 DateTime = '20130228';
SELECTCAST (
DATEPART(YEAR, DATEADD(Day, DATEDIFF(Day, @D1, @D2), 0)) - 1900.0 +-- # years
DATEPART(dy, DATEADD(Day, DATEDIFF(Day, @D1, @D2), -1 )) / 1000.0-- Datepart
as decimal(5,3))
Output is 2.001 but actually it should be only 2 years so my output should be 2.000
February 17, 2015 at 1:20 am
Guitar_player (2/17/2015)
DECLARE @D1 DateTime = '20090128',
@D2 DateTime = '20150217';
SELECTCAST (
DATEPART(YEAR, DATEADD(Day, DATEDIFF(Day, @D1, @D2), 0)) - 1900.0 +-- # years
DATEPART(dy, DATEADD(Day, DATEDIFF(Day, @D1, @D2), -1 )) / 1000.0-- Datepart
as decimal(5,3))
But this wont give me perfect output when a leap year comes up ..as pls check this
DECLARE @D1 DateTime = '20110228',
@D2 DateTime = '20130228';
SELECTCAST (
DATEPART(YEAR, DATEADD(Day, DATEDIFF(Day, @D1, @D2), 0)) - 1900.0 +-- # years
DATEPART(dy, DATEADD(Day, DATEDIFF(Day, @D1, @D2), -1 )) / 1000.0-- Datepart
as decimal(5,3))
Output is 2.001 but actually it should be only 2 years so my output should be 2.000
Phil's solution looks like it works for this case.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 17, 2015 at 1:21 am
Hi Phil,
Your query looks good..but can u check with this pls..I am getting the same output for the change in the value of D1
declare @D1 datetime = '20120229' -- Replace with this value '20120228' and execute and check
,@D2 datetime = '20130228';
declare @Yrs int
,@dys int;
set @Yrs = datediff(year, @D1, @D2);
set @dys = datediff(day, dateadd(year, @Yrs, @D1), @D2)
select Years = @Yrs
,Days = @dys
,Fraction = @Yrs + @dys / 1000.0;
I have googled and got one query which i think is good even at leap year times too , could you experts check this please
DECLARE @date datetime, @tmpdate datetime, @years int, @months int, @days int, @enddate datetime
SET @date = '20120229' -- Replace with this value '20120228' and execute and check
SET @enddate = '20130228'
SELECT @tmpdate = @date
SELECT @years = DATEDIFF(yy, @tmpdate, @enddate) - CASE WHEN (MONTH(@date) > MONTH(@enddate)) OR (MONTH(@date) = MONTH(@enddate) AND DAY(@date) > DAY(@enddate)) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(yy, @years, @tmpdate)
--SELECT @months = DATEDIFF(m, @tmpdate, '20110525') - CASE WHEN DAY(@date) > DAY('20110525') THEN 1 ELSE 0 END
--SELECT @tmpdate = DATEADD(m, @months, @tmpdate)
SELECT @days = DATEDIFF(d, @tmpdate, @enddate)
SELECT Cast(@years as varchar(3))+'.'+Right('00'+Cast(@days as Varchar(3)),3)
February 17, 2015 at 1:29 am
Guitar_player (2/17/2015)
Hi Phil,Your query looks good..but can u check with this pls..I am getting the same output for the change in the value of D1
declare @D1 datetime = '20120229' -- Replace with this value '20120228' and execute and check
,@D2 datetime = '20130228';
declare @Yrs int
,@dys int;
set @Yrs = datediff(year, @D1, @D2);
set @dys = datediff(day, dateadd(year, @Yrs, @D1), @D2)
select Years = @Yrs
,Days = @dys
,Fraction = @Yrs + @dys / 1000.0;
It can be argued that this is the correct answer ... The period from 1 March 2012 to 28 Feb 2013 is exactly one year.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 17, 2015 at 1:31 am
Didn't think about leap years in the calculation,
This one might be better,
DECLARE @D1 DateTime = '20110228',
@D2 DateTime = '20130227';
DECLARE @Years int = datediff(year, @D1, @D2);
DECLARE @Days int = datediff(day, dateadd(year, @Years, @D1), @D2)
IF @Days < 0
BEGIN;
SET @Years = @Years - 1;
SET@Days = datediff(day, dateadd(year, @Years, @D1), @D2)
END
SELECT @Years, @Days, @Years + @Days / 1000.0
February 17, 2015 at 1:50 am
Maybe what you need to try is an age calculation to get the years and then a second calculation to get the days.
I used to refer people to a post by Adam Machanic on SolidQ but that seems to have disappeared.
I'm not a big fan of StackOverflow, but there is this page and the solution attributable to Ed Harper look close to what I recall Adam Machanic doing.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 17, 2015 at 2:11 am
I am Sorry Phil, if at all i hurt you !!!!
but my check is that if we are really looking for the difference then this should be the output i believe
@Startdate || @EndDate || Output
20120228 || 20130228 || 1.000
20120229 || 20130228 || 0.365
20120228 || 20130228 || 1.000 -- On using your query Phil
20120229 || 20130228 || 1.000
So i am asking you dont mind plss..
@louis -- I will check and surely let you know
@dwain -- I will check the link you have shared and will let you know if that was helpful
February 17, 2015 at 2:38 am
SELECT CAST(
CASE SIGN(DATEPART(dy,@Enddate)-DATEPART(dy,@Startdate))
WHEN 0 THEN DATEDIFF(year,@Startdate,@Enddate)
WHEN 1 THEN DATEDIFF(year,@Startdate,@Enddate)+(DATEPART(dy,@Enddate)-DATEPART(dy,@Startdate))/1000.0
ELSE (DATEDIFF(year,@Startdate,@Enddate)-1)+(DATEDIFF(day,DATEADD(year,DATEDIFF(year,@Startdate,@Enddate)-1,@Startdate),@Enddate))/1000.0
END as decimal(9,3))
Far away is close at hand in the images of elsewhere.
Anon.
February 17, 2015 at 2:46 am
Guitar_player (2/17/2015)
I am Sorry Phil, if at all i hurt you !!!!but my check is that if we are really looking for the difference then this should be the output i believe
@Startdate || @EndDate || Output
20120228 || 20130228 || 1.000
20120229 || 20130228 || 0.365
20120228 || 20130228 || 1.000 -- On using your query Phil
20120229 || 20130228 || 1.000
So i am asking you dont mind plss..
@louis -- I will check and surely let you know
@dwain -- I will check the link you have shared and will let you know if that was helpful
Hehe, a bit of dodgy SQL is not going to hurt me:-) Now I see your point. A tricky one to handle.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 17, 2015 at 2:53 am
Noticed something when I was doing some testing.
Adding 1 year to 2012-02-29 returns 2013-02-28
SELECT dateadd(year, 1, '20120229')
February 17, 2015 at 2:59 am
Louis Hillebrand (2/17/2015)
Noticed something when I was doing some testing.Adding 1 year to 2012-02-29 returns 2013-02-28
SELECT dateadd(year, 1, '20120229')
What would you expect?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 17, 2015 at 3:10 am
Phil,
This could be expected, I just wanted to point this out to the OP.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply