A question from the blog question:
My database a wants to return the time difference between the two times. The format of the two is as follows:
A B
11:00:00 12:20:11
Then, the returned format is
C
1:20:11
I use datediff, but I cannot get my results. The format is not the same. How can I write it?
My answer:
Select Cast ( Datediff (Hh, ' 11:00:00 ' , ' 12:20:11 ' ) As Varchar )
+ ' : ' +
Cast (( Datediff (MI, ' 11:00:00 ' , ' 12:20:11 ' ) % 60 ) As Varchar )
+ ' : ' +
Cast (( Datediff (Ss, ' 11:00:00 ' , ' 12:20:11 ' ) % 60 ) As Varchar )
If the returned format is 01:20:11CodeAs follows:
Select Right ( ' 0 ' + Cast ( Datediff (Hh, ' 11:00:00 ' , ' 12:20:11 ' ) As Varchar ), 2 )
+ ' : ' +
Right ( ' 0 ' + Cast (( Datediff (MI, ' 11:00:00 ' , ' 12:20:11 ' ) % 60 ) As Varchar ), 2 )
+ ' : ' +
Right ( ' 0 ' + Cast (( Datediff (Ss, ' 11:00:00 ' , ' 12:20:11 ' ) % 60 ) As Varchar ), 2 )
The answer is from: how to display hh: Mm format using datediff () function.
Thanks to Mu Feng for providing a simpler solution:
Select Convert ( Varchar , Convert ( Datetime , Datediff (Ss, ' 11:00:00 ' , ' 12:20:11 ' ) / Convert ( Decimal , 86400 )), 8 )