Convert date to dd/mm/yyyy hh:mm:ss format

[lpatnaik]

I need to convert the date from a datetime field of a table in sql server to the format "dd/mm/yyyy hh:mm:ss". Can anyone help me out.

Regards
Lopa

 

[JamesLean]

SELECT CONVERT(varchar(10), datecol, 103) + ' ' + CONVERT(varchar(10), datecol, 108)
FROM mytable

--James

 

[lpatnaik]

Hi, That is great!!!. But i need the hour in the other format. i.e. if it is 7:30 pm, it should display 07:30:00 PM and not 19:30:00 PM
Can you get around that?
Pls help its urgent

 

[ClaireHCM]

You could use the following query,but it doesnt really look simple.

SELECT CONVERT(VARCHAR(10),GETDATE(),103)+' '++right('0'+ltrim(SUBSTRING(CONVERT(varchar(35),getdate(),109),13,8)),8)+' '+SUBSTRING(CONVERT(varchar(35),getdate(), 109),25,3)