DateDiff to show Minutes and Seconds

Question

I need to calculate the diff and have my answer show minutes and seconds. Here is sample

Declare @clockin datetime, @clockout datetime, @total decimal(18,4)
Set @clockin = '2015-01-03 08:15:19.000'
Set @clockout = '2015-01-03 12:02:42.000'

Set @total = DateDiff(minute, @clockin, @clockout)

But this returns a whole number I need it to show seconds as well.

Answer 1

I hope this approach can help you, the solution in this way is simple, we just get the quantity of seconds in between, then we calculate the minutes by a simple division and seconds with the module operator.

Declare @clockin datetime, @clockout datetime, @totalSeconds int
Set @clockin = '2015-01-03 08:15:19.000'
Set @clockout = '2015-01-03 12:02:42.000'

Select @totalSeconds = Cast((@clockout - @clockin) as Float) * 24.0 * 60 *60

Select @totalSeconds /60 as [Minutes], @totalSeconds % 60 as [Seconds]

If you want to extend your approach, just change it a bit calculating the differece in seconds but minutes and doing the same like above to separate minutes and seconds, it would look like this:

Declare @clockin datetime, @clockout datetime, @total int
Set @clockin = '2015-01-03 08:15:19.000'
Set @clockout = '2015-01-03 12:02:42.000'

Set @total = DateDiff(second, @clockin, @clockout)
Select @total/60 as [Minutes], @total % 60 as [Seconds]

Result:

Minutes: 227, Seconds: 23

Answer 2

play with this as you wish, the real issue is DATEDIFF if you hover over only returns and int

Declare @clockin datetime, @clockout datetime, @total datetime
declare @realtotal decimal(18,4)
Set @clockin = '2015-01-03 08:15:19.000'
Set @clockout = '2015-01-03 12:02:42.000'


select @total =  dateadd(ss, DATEDIFF(ss, @clockin, @clockout), cast(0 as DateTime))
select @total
select @realtotal =  datepart(hour,@total)*60 + datepart(minute,@total) + (datepart(second,@total)*.01)
select @realtotal