I have 2 tables: table 1 and table 2.
One of the columns is the
date
01/07/2016 00:23:45
07/01/2016 12:23:45 AM
07/01/2016 00:23:45
Assuming you don't have second in your values you can use below conversion
declare @dt varchar(100) = '01/07/2016 17:23:45'
select LEFT(@dt, 10) + ' ' + replace(right(CONVERT(varchar, cast(@dt as datetime), 109), 13), ':000', ' ')
it will result this
01/07/2016 5:23:45 PM
and for this data '01/07/2016 07:23:45'
will result
01/07/2016 7:23:45 AM
I did not find any straight way using CONVERT function