shan shan - 7 months ago 11
SQL Question

Time part is missing when using convert

I have a date in a column like this (

2016-06-01 21:50:00.000
) -
YYYY-MM-DD HH:MM:SS


I am trying to convert it to like this --> (
01/06/2016 21:50:00.000
)
DD-MM-YYYY HH:MM:SS


I've tried the following;

SELECT CONVERT(VARCHAR(30), CONVERT(DATETIME, MYDATECOLUMN, 101), 103)
FROM MYTABLE


Time part is missing.

Can anyone please suggest how to achieve this?

I don't have permission to change data in the table

Answer

try below code

select CONVERT(VARCHAR(10), MYDATECOLUMN, 103) + ' '  + convert(VARCHAR(8), MYDATECOLUMN, 14) 
FROM MYTABLE

IF you want to show millisecond also change varchar size from 8 to 12 in second part

  select CONVERT(VARCHAR(10), MYDATECOLUMN, 103) + ' '  + convert(VARCHAR(12), MYDATECOLUMN, 14) 
   FROM MYTABLE