Calculating difference between two times 2

[shaunhubbs]

Hi all,

I have times stored as integers (Eg. 2351 = 11:51 PM, 955 = 9:55 AM) and I need to calclute the amount of minutes between them. Anyone have any suggestions?

TIA.

- Shaun

 

[amrita418]

How is 12:02 AM represented?

As 2?

Regards,
AA

 

[amrita418]

Also what would 132 mean? 1:02 PM or 1:32 AM?

 

[shaunhubbs]

Yes, 12:02 AM = 2 and 132 = 1:32 AM

 

[amrita418]

Oops my bad,
I inteded to ask you if 132 meant 13:02 or 1:32?

 

[shaunhubbs]

132 = 1:32 AM and 1302 = 1:02 PM

 

[amrita418]

Kinda ugly but should work:

declare @start int,
@end int
set @start = 955
set @end = 2351
select datediff(mi, convert(datetime, case when len(@start) = 4 then left(@start, 2) + ':' + right(@start, 2)
                                           when len(@start) = 3 then left(@start, 1) + ':' + right(@start, 2)
	                                   else '00:' + convert(varchar, @start) end), 
                    convert(datetime, case when len(@end) = 4 then left(@end, 2) + ':' + right(@end, 2)
                                           when len(@end) = 3 then left(@end, 1) + ':' + right(@end, 2)
	                                   else '00:' + convert(varchar, @end) end))

PS: Will update you with an better one.
Regards,
AA

 

[shaunhubbs]

Works great! Thanks a lot AA.

I modified it slightly in case a time was just after midnight (meaning the length of the time would actually be 1 character). I will post here in case anyone runs into a similar problem:

SELECT @temp_length = datediff(mi,
convert(datetime, case when len(@proc_start) = 4 then left(@proc_start, 2) + ':' + right(@proc_start, 2)
when len(@proc_start) = 3 then left(@proc_start, 1) + ':' + right(@proc_start, 2)
when len(@proc_start) = 2 then '00:' + convert(varchar, @proc_start)
else '00:0' + convert(varchar, @proc_start)
end),
convert(datetime, case when len(@proc_end) = 4 then left(@proc_end, 2) + ':' + right(@proc_end, 2)
when len(@proc_end) = 3 then left(@proc_end, 1) + ':' + right(@proc_end, 2)
when len(@proc_end) = 2 then '00:' + convert(varchar, @proc_end)
else '00:0' + convert(varchar, @proc_end)
end))

 

[shaunhubbs]

Oh and previous to that posted SELECT statement I had to manipulate the numbers a little in case the procedure started before midnight and ended after:

IF @proc_end < @proc_start
BEGIN
SELECT @proc_end = @proc_end + 1200
SELECT @proc_start = @proc_start - 1200
END

 

[amrita418]

I do not think you need another case for len = 2 since 2 or less is taken care of in the else stmt.

Regards,
AA

 

[shaunhubbs]

I needed the extra zero after the ':' so it actually did require that last statement otherwise if my time was '2' then it would put '00:2' which was more like 20 minutes after midnight than two minutes after midnight.

Thanks!

 

[amrita418]

I do not think that is true.

check this out:

declare 
@start int
set    @start = 2
select convert(datetime, case when len(@start) = 4 then left(@start, 2) + ':' + right(@start, 2)
                              when len(@start) = 3 then left(@start, 1) + ':' + right(@start, 2)
	                          else '00:' + convert(varchar, @start) end)

Regards,
AA

 

[shaunhubbs]

That's alright then. Thanks again.

 

[vongrunt]

Um... how about plain ol cryptic integer/modulo math?

select @end/100*60 + @end%100 - (@start/100*60 + @start%100)

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[shaunhubbs]

That is fantastically simple. Thanks a lot for that solution as well.