In SQL Server 2012 and up you can use FORMAT():
SELECT FORMAT(CURRENT_TIMESTAMP, 'yyyy-MM-dd hh:mm:ss tt')
For example, in earlier versions you might have needed to concatenate two or more separate DateTime conversions to acquire what you needed:
SELECT
CONVERT(CHAR(10), CURRENT_TIMESTAMP, 23) + ' ' +
RIGHT('0' + LTRIM(RIGHT(CONVERT(CHAR(20), CURRENT_TIMESTAMP, 22), 11)), 11);
See the Date and Time Styles section of CAST and CONVERT (Transact-SQL) for all of the built-in formatting styles.
If you need to know more about MS SQL, it is recommended to go for the SQL Certification Course today.