SELECT 
    CONVERT(VARCHAR(10),attnc_chkin_dt,101) as INDATE,
    CONVERT(VARCHAR(10),attnc_chkin_dt,108) as TimePart
FROM pmt_attendance

o/p

indate   04/18/2015

time part :17:45:00

i need to convert this 17:45:00 to 12 hours date for mat

please  give the answer its urgent

You can work the following code and tweak it to suit your requirement:

select CONVERT(varchar, dateadd(hour, 6, getdate()), 109)

Good Luck !

SELECT CONVERT(VARCHAR(20),getdate(),100)

SELECT CONVERT(varchar(20),  CAST('Apr 21 2015 13:57:52' AS TIME), 100)

• Edited by dave_gona 25 minutes ago

If its SQL 2012 or later use

SELECT FORMAT(attnc_chkin_dt,'MM/dd/yyyy hh:m:ss tt')
FROM pmt_attendance

SELECT REPLACE(REPLACE(LTRIM(RIGHT(CONVERT(VARCHAR(32), 
CONVERT(SMALLDATETIME, '17:45'), 0),7)), 'AM', ' AM'), 'PM', ' PM');

SELECT CONVERT(VARCHAR(20),getdate(),100)

SELECT CONVERT(varchar(20),  CAST('Apr 21 2015 13:57:52' AS TIME), 100)

• Edited by dave_gona Tuesday, April 21, 2015 6:39 AM

Hi Muhasin,

Please reference below sample.

DECLARE @attnc_chkin_dt DATETIME
SET @attnc_chkin_dt='20150418 17:45:00'

SELECT 
    CONVERT(VARCHAR(10),@attnc_chkin_dt,101) as INDATE,
    CONVERT(VARCHAR(10),CAST(@attnc_chkin_dt AS TIME),100) as TimePart

/*output
  INDATE	TimePart
04/18/2015	5:45PM
*/

If you have any qeustion, feel free to let me know.