I have the following query
SELECT CONVERT(TIME, LEFT(CONVERT(VARCHAR,(DATEDIFF(HOUR,[Hora Salida],[Registro Salida]))),2) + ':' + LEFT(CONVERT(VARCHAR,DATEDIFF(MINUTE,[Hora Salida],[Registro Salida])),2) + ':00')
but when I try to execute it, it throws me the following error
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
What I want is for me to join the hour and the minutes but it throws me the error mentioned above, help
There are several problems in your query.
First of all, you must understand how DATEDIFF works. In your code you are trying to concatenate DATEDIFF(HOUR,... with DATEDIFF(MINUTE... , but this would bring you incorrect results in many cases. For example, if the start time is 09:55 and end time is 10:06 , DATEDIFF(HOUR,... will deliver 1 even though one hour has not passed; this is because one time was at 9 and the other at 10.
Another problem you have is to use VARCHAR without specifying the length you want; you should always put the length as a parameter ( VARCHAR(20) for example), otherwise you will find unexpected results.
Having said all of the above, the code could simply be the following:
SELECT CONVERT(time,DATEADD(MILLISECOND,DATEDIFF(SECOND,[Hora Salida],[Registro Salida])*1000,0),114)