Friday, February 17, 2012

Date calculation

I need to create a user defined function to calculation the difference between today and a future date. The result needs to be in days, hours, and minutes formatted as per the following example: 1d / 4h / 30m. I have a moderate level of SQL exprience. however, I would appreciate some expert advice as the best way to approach this.So do you have a specific question? Are you having an issue with some part of it?|||I am not having a specific issue. I wrote the following and it works:

CREATE FUNCTION [simexdb].GetRoundTimeLeft
(
@.datetoday datetime,
@.RoundExpDate datetime
)
RETURNS varchar(50) AS
BEGIN
DECLARE @.Days int
DECLARE @.Hours int
DECLARE @.Min int
DECLARE @.TimeString as varchar(50)
SET @.Min = DATEDIFF ( mi , @.datetoday, @.RoundExpDate)
SET @.Days= @.Min/(24*60)
SET @.Min = @.Min - (@.Days*(24*60))
SET @.Hours= @.Min/60
SET @.Min = @.Min - (@.Hours*(60))
SET @.TimeString = CONVERT(varchar, @.Days ) + 'd / ' + CONVERT(varchar, @.Hours ) + 'h / ' + CONVERT(varchar, @.Min )+ 'm'
Return @.TimeString
END

I would like some expert feedback as to whether this is the best and most efficient approach.

Thank you for your response.|||That looks good|||Thank you for your confirmation.

No comments:

Post a Comment