-
Returning Time Difference As Days/ Hours/ Minutes
I have written a function that returns the number of Days, Hours and minutes from a given number of minutes. On testinf the results are close but not quite there. Can anyone see where I have gone wrong or is there an easier way of doing this? Code is as follows:
CREATE FUNCTION dbo.GetTimeBetweenLong
(@StartTime DateTime, @EndTime DateTime, @CurrentDate DateTime)
RETURNS VarChar(50) AS
BEGIN
DECLARE @TotalTime Numeric
DECLARE @Minutes Numeric
DECLARE @Hours Numeric
DECLARE @Days Numeric
DECLARE @MinutesInDays Numeric
IF @EndTime IS NULL
BEGIN
SET @Days = DATEDIFF(Day, @StartTime, @CurrentDate)
SET @Hours = DATEDIFF(Hour, @StartTime, @CurrentDate) - (@Days * 24)
SET @Minutes = DATEDIFF(Minute, @StartTime, @CurrentDate) - ((@Days * 24)*60) - (@Hours * 60)
END
ELSE
BEGIN
SET @Days = DATEDIFF(Day, @StartTime, @EndTime)
SET @Hours = DATEDIFF(Hour, @StartTime, @EndTime) - (@Days * 24)
SET @Minutes = DATEDIFF(Minute, @StartTime, @EndTime) - ((@Days * 24)*60) - (@Hours * 60)
END
IF(@Days <0)
BEGIN
SET @Days = @Days - @Days - @Days
END
IF (@Hours < 0)
BEGIN
SET @Hours = @Hours - @Hours - @Hours
END
IF (@Minutes <0)
BEGIN
SET @Minutes = @Minutes - @Minutes - @Minutes
END
RETURN CONVERT(nVarChar(10),@Days) + ' Days, ' + CONVERT(nVarChar(5), @Hours) + ' Hours, ' + CONVERT(nVarCHar(5), @Minutes) + ' Mins'
END
-
How about this instead:
DECLARE @dt1 datetime, @dt2 datetime
SET @dt1 = CONVERT(datetime, '2005-09-14 00:00:000')
SET @dt2 = CONVERT(datetime, '2005-09-16 10:30:000')
SELECT CONVERT(varchar, DATEDIFF(d, 0, DATEADD(mi, DATEDIFF(mi, @dt1, @dt2), 0))) + ' days; ' +
CONVERT(varchar, DATEPART(hh, CONVERT(varchar(8), DATEADD(mi, DATEDIFF(mi, @dt1, @dt2), 0), 108))) + ' hours; ' +
CONVERT(varchar, DATEPART(mi, CONVERT(varchar(8), DATEADD(mi, DATEDIFF(mi, @dt1, @dt2), 0), 108))) + ' minutes'
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|