Quote:But the intime is greater than 24 hours so that it shows conversion failed error....
There is no such time as 34 hours. So what does an InDate = '34:00:00' really mean?
If it means "34 hours after a set time" then you can do something like
declare @basetime time = '00:00:00'
declare @intime time = dateadd(hh,34, @basetime)
So @intime ends up as '10:00:00' - but on what day? It's meaningless data, it imparts no information.
So the advice given by @Richard-MacCutchan is spot on - store this information in
declare @inday datetime = '2021-03-08 00:00:01.000'
declare @outday datetime = '2021-03-09 01:10:05.000'
You can then use standard functions to get the time differences as integers
. For example
declare @diffWholeHOURS int = (select DATEDIFF(HOUR, @inday, @outday))
declare @diffRemainingMinutes int = ((select DATEDIFF(MINUTE, @inday, @outday)) - (@diffWholeHOURS) *60)
The key thing to note here is that I'm not using any clunky and unnecessary conversions and I'm using the correct data types throughout.
Your requirement to get the results in HH:MM format is probably leading you astray. It goes back to your InDate = '34:00:00' problem. That semi-colon seems to be making you think that you are going to get a "
" back. You are not. It's a contrived display format
so that needs to be the very last thing you do. In fact, it should really be done in your UI layer. But you could do
select FORMAT(@diffWholeHOURS, '00') + ':' + FORMAT(@diffRemainingMinutes, '00')