April 11, 2012 at 7:11 pm
[font="Arial"]Hello All,
There may be millions of way calculating age to get years,months,days,HH,MM,Secs. Here I thought of adding one way of doing it. Hope it will be helpful.
--Calculate Age.
CREATE FUNCTION [dbo].[FN_getagecalculated]
(
@Startdate Datetime,
@Enddate datetime
)
/*
To get the elapsed time between two datetime values it will
return as (YY:MM:DD:HH:MM:SS)
*/
Returns VARCHAR(100)
AS
BEGIN
DECLARE @INT BIGINT
DECLARE @result varchar(100)
SET @INT = DATEDIFF(SECOND ,@Startdate,@Enddate)
SET @result =
convert(varchar(10), (@INT/31556926)) +':' + --years
RIGHT('0'+convert(varchar(10), ((@INT%31556926)/2629744)),2)+':' -- Months
+ RIGHT('0'+convert(varchar(10), (((@INT%31556926)%2629744)/86400)),2) + ':' --days
+ RIGHT('0'+convert(varchar(10), ((((@INT%31556926)%2629744)%86400)/3600)),2)+':' -- hrs
+ RIGHT('0'+convert(varchar(10), (((((@INT%31556926)%2629744)%86400)%3600)/60)),2)+':'--mm
+ RIGHT('0'+convert(varchar(10), (((((@INT%31556926)%2629744)%86400)%3600)%60)),2)--Secs
RETURN @result
END
--Checking
--SELECT [dbo].[FN_getagecalculated] ('2010-04-11 20:45:43.610','2011-04-12 20:45:43.610') as [ Elapsed Time(YY:MM:DD:HH:MM:SS)]
Now the below function will give the elapsed time with DD:HH:MM:SS
ALTER FUNCTION [dbo].[FN_getElapsedTime]
(
@Startdate Datetime,
@Enddate datetime
)
/*
To get the elapsed time between two datetime values it will
return as (DD:HH:MM:SS)
*/
Returns VARCHAR(100)
AS
BEGIN
DECLARE @INT INT
DECLARE @result varchar(100)
SET @INT = DATEDIFF(SECOND ,@Startdate,@Enddate)
SET @result =
convert(varchar(10), (@INT/86400)) + ':'
+ RIGHT('0'+convert(varchar(10), ((@INT%86400)/3600)),2) + ':' --hh
+ RIGHT('0'+convert(varchar(10), (((@INT%86400)%3600)/60)),2) + ':' --mm
+ RIGHT('0'+convert(varchar(10), (((@INT%86400)%3600)%60)),2) --sec
RETURN @result
END
SELECT [dbo].[FN_getElapsedTime] ('2011-01-06 10:30:23','2011-01-06 18:00:43') as [ Elapsed Time(DD:HH:MM:SS)]
Hope it helps .
Thanks
April 11, 2012 at 8:49 pm
Jeez, Louise! All that work for a one-liner?
DECLARE
@Startdate Datetime,
@Enddate datetime
SELECT @Startdate = '1958-06-18' -- My birthday in case someone wants to send me something
,@Enddate = GETDATE() -- Now
SELECT REPLACE(REPLACE(SUBSTRING(CONVERT(CHAR(21),
DATEADD(second, DATEDIFF(second, @Startdate, @Enddate), 0),20) , 3, 17),
' ', ':'), '-', ':')
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
April 11, 2012 at 9:13 pm
It seems to be getting two days additional the code. Can you please let me know whether i doing correct.
Result i got is
53:10:26:22:11:18
But it seems to be 24 days difference between 18 of last month to current date.
is my view is correct
April 11, 2012 at 9:29 pm
Hi,
I tried using the below input and i am getting
DECLARE
@Startdate Datetime,
@Enddate datetime
SELECT @Startdate = '2011-05-20' ,@Enddate = '2011-05-24'
SELECT REPLACE(REPLACE(SUBSTRING(CONVERT(CHAR(21),
DATEADD(second, DATEDIFF(second, @Startdate, @Enddate), 0),20) , 3, 17),
' ', ':'), '-', ':')
Result i got was
00:01:05:00:00:00
I think it should be 00:00:04:00:00:00
April 11, 2012 at 9:43 pm
Ooops. Sorry... what was I thinking!
Hold on a minute while I look at it again.
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
April 11, 2012 at 10:16 pm
Still a one liner but not quite as elegant, although I think this time it will work:
DECLARE
@Startdate Datetime,
@Enddate datetime
SELECT @Startdate = '2011-05-20' ,@Enddate = '2011-05-24'
SELECT CAST(DATEDIFF(year, @Startdate, @Enddate) AS VARCHAR(5))
+':'+CAST(DATEDIFF(month, DATEADD(year, DATEDIFF(year, @Startdate, @Enddate), @startdate), @Enddate) AS VARCHAR(5))
+':'+CAST(DATEDIFF(day, DATEADD(month, DATEDIFF(month, @Startdate, @Enddate), @startdate), @Enddate) AS VARCHAR(5))
+':'+CONVERT(CHAR(21),DATEADD(second, DATEDIFF(second, @Startdate, @Enddate), 0),8)
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
April 12, 2012 at 6:17 am
Hi ,
I tried using the below input and it returning
SELECT @Startdate = '1958-06-18' ,@Enddate = '2011-05-24'
Result as 53:-1:6:00:00:00
It seems to be wrong.
April 12, 2012 at 6:27 am
At this point, I'd say scrap my suggestions and go with your original.
Can't always be right.
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply