Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- @start_date = '2009-04-15 10:24:00.000'
- @end_date = '2009-04-16 19:43:01.000'
- DATEDIFF(minute, @start_date, @end_date)
- CONVERT(varchar,(@end_date-@start_date),108)
- DECLARE @ElapsedS INT
- SET @ElapsedS = DATEDIFF(second, @start_date, @end_date)
- CREATE FUNCTION [dbo].[udfTimeSpanFromSeconds]
- (
- @Seconds int
- )
- RETURNS varchar(15)
- AS
- BEGIN
- DECLARE
- --Variable to hold our result
- @DHMS varchar(15)
- --Integers for doing the math
- , @Days int --Integer days
- , @Hours int --Integer hours
- , @Minutes int --Integer minutes
- --Strings for providing the display
- , @sDays varchar(5) --String days
- , @sHours varchar(2) --String hours
- , @sMinutes varchar(2) --String minutes
- , @sSeconds varchar(2) --String seconds
- --Get the values using modulos where appropriate
- SET @Hours = @Seconds/3600
- SET @Minutes = (@Seconds % 3600) /60
- SET @Seconds = (@Seconds % 3600) % 60
- --If we have 24 or more hours, split the @Hours value into days and hours
- IF @Hours > 23
- BEGIN
- SET @Days = @Hours/24
- SET @Hours = (@Hours % 24)
- END
- ELSE
- BEGIN
- SET @Days = 0
- END
- --Now render the whole thing as string values for display
- SET @sDays = convert(varchar, @Days)
- SET @sHours = RIGHT('0' + convert(varchar, @Hours), 2)
- SET @sMinutes = RIGHT('0' + convert(varchar, @Minutes), 2)
- SET @sSeconds = RIGHT('0' + convert(varchar, @Seconds), 2)
- --Concatenate, concatenate, concatenate
- SET @DHMS = @sDays + ':' + @sHours + ':' + @sMinutes + ':' + @sSeconds
- RETURN @DHMS
- END
- SELECT TimeSpan = dbo.udfTimeSpanFromSeconds(@ElapsedS)
Add Comment
Please, Sign In to add comment