CONVERT CAST dates in T-SQL

Sometimes you need to compare dates as text rather than in date format, and this can be especially useful when comparing dates from different systems using different data types. But T-SQL makes it easy to format different dates using the CONVERT function.

For example, if you have a date stored as text in the dd/mm/yyyy format, then you can convert a standard datetime format from yyyy-mm-dd hh:mm:ss.sss using the native style formats.
http://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/

SELECT convert(varchar, getdate(), 100)- mon dd yyyy hh:mmAM
CONVERT(varchar,CloseDate__c,100) AS FormattedCloseDate
Other formats include:
convert(varchar, getdate(), 101) - mm/dd/yyyy
convert(varchar, getdate(), 102) – yyyy.mm.dd
convert(varchar, getdate(), 103) – dd/mm/yyyy
convert(varchar, getdate(), 104) – dd.mm.yyyy
convert(varchar, getdate(), 105) – dd-mm-yyyy
convert(varchar, getdate(), 106) – dd mon yyyy
convert(varchar, getdate(), 107) – mon dd, yyyy
convert(varchar, getdate(), 108) – hh:mm:ss
convert(varchar, getdate(), 109) – mon dd yyyy hh:mm:ss:mmmAM
convert(varchar, getdate(), 110) – mm-dd-yyyy
convert(varchar, getdate(), 111) – yyyy/mm/dd
convert(varchar, getdate(), 112) – yyyymmdd
convert(varchar, getdate(), 113) – dd mon yyyy hh:mm:ss:mmm
convert(varchar, getdate(), 114) – hh:mm:ss:mmm(24h)
convert(varchar, getdate(), 120) – yyyy-mm-dd hh:mm:ss(24h)
convert(varchar, getdate(), 121) – yyyy-mm-dd hh:mm:ss.mmm
convert(varchar, getdate(), 126) – yyyy-mm-ddThh:mm:ss.mmm

See also http://www.w3schools.com/sql/func_convert.asp
and http://msdn.microsoft.com/en-us/library/ms187928.aspx