DateTime value not converted to specific format in SQL-CodePudding

I am trying to convert the value "2018-05-18 09:57:00" to May 18 2018 9:57AM. Below is my SQL and I have tried different methods but still showing the same result.

SELECT 
   b.BookingID ,
   (SELECT CONCAT(il.CarrierCode,'(', CONVERT(VARCHAR(10),il.DeparDate,0)')')
FROM Booking b
LIMIT 1

But this is working when I pass GETDATE() and not working with my column. Please help me. I am using Microsoft SQL Server 2017.

Thank you

CodePudding user response：

use this formatting CONVERT(varchar,[datefield],100) to get the desired format of your datetime field. if the data type of your field is string, cast it to datetime so this sql date formatting will work.

SELECT b.BookingID ,(SELECT CONCAT(il.CarrierCode,'(', convert(varchar,cast(il.DeparDate as datetime), 100)')')
FROM Booking b