February 17, 2015 at 6:35 pm
You'll need to decide for yourself whether this is correct for you or not:
WITH SampleDates (Date1, Date2) AS
(
SELECT '1971-02-15', '2009-12-09'
UNION ALL SELECT '2012-02-29', '2015-02-28' -- 1.000 years?
UNION ALL SELECT '2012-02-28', '2015-02-28' -- 1.000 years?
)
SELECT Years=Years + DATEDIFF(day, DATEADD(year, Years, Date1), Date2)/1000.
FROM SampleDates a
CROSS APPLY
(
SELECT Years=CASE
WHEN DATEADD(year, DATEDIFF (year, Date1, Date2), Date1) > Date2
THEN DATEDIFF (year, Date1, Date2) - 1
ELSE DATEDIFF (year, Date1, Date2)
END
) b;
This code is adapted from here: Age calculation with SQL Server
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 8:09 pm
CELKO (2/17/2015)
I am not an expert in SQL , but i been trying to get the difference of two dates in years.days format ..
You are the kid in the geography class who did not get the message about the earth being round. :w00t: In any tiered architecture, like SQL, display formatting is done in a presentation layer, never in the database layer
I would probably get the month count, but even with that, do you want full month completed or does a partial month count? The hard part of RDBMS is getting specs 😉
Round? Whoever said the Earth was round? It must be flat because I keep falling off the edge! :w00t:
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 11:54 pm
CELKO (2/17/2015)
Round? Whoever said the Earth was round? It must be flat because I keep falling off the edge! :w00t:
You are not really drunk if you still hang on to ground without falling off:-D Of course, I tend to slide to one side or the other when I slosh my head ... :Whistling:
I get vertigo from hanging upside down like a bat when visiting Australia. Does that count?
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 18, 2015 at 1:58 am
Well, i found this query really useful where not much hard coding is done and we are getting the exact output
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)
without much hardcoding ... 🙂 which i have started using in my work progress..
@@ Celko : Your correct...i am just a guy who started trying to know about the earth, and this is actually been used in warehouse db to make reporting purpose easfull 🙂
and I am really glad that these many experts are here to help me out Thanks once again to ALL of you for your time 🙂
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply