Date formatting in SQL Server


select getdate()
will return the current datetime or timestamp of the system in the following format in sqlserver 
2009-09-09 10:16:06.117

Converting this datetime to dd mon yyyy hh:mi:ssAM( or PM) format in sqlserver
select convert(varchar(11), getdate(), 113) + right(convert(varchar(20), getdate(), 100), 8)

select convert(varchar, getdate(), 113) gives the datetime in dd mon yyyy hh:mi:ss:mmm(24h) format
09 Sep 2009 10:27:32:137

converting to varchar(11) keeps only the first 11 character. So it becomes
09 Sep 2009

select convert(varchar(20), getdate(), 100) gives the datetime in mon dd yyyy hh:miAM (or PM)
Sep 9 2009 10:29AM

So getting the rightmost 8 characters gives 10:29AM

Combining these two we can get a customized datetime format from sqlserver that is not directly provided by sqlserver datetime convertion format