To see the effects, just run this script against your database:

PRINT ‘1) HERE IS MON DD YYYY HH:MIAM (OR PM) FORMAT ==>’ +
CONVERT(CHAR(19),GETDATE()) 
PRINT ‘2) HERE IS MM-DD-YY FORMAT ==>’ +
CONVERT(CHAR(8),GETDATE(),10) 
PRINT ‘3) HERE IS MM-DD-YYYY FORMAT ==>’ +
CONVERT(CHAR(10),GETDATE(),110)
PRINT ‘4) HERE IS DD MON YYYY FORMAT ==>’ +
CONVERT(CHAR(11),GETDATE(),106)
PRINT ‘5) HERE IS DD MON YY FORMAT ==>’ +
CONVERT(CHAR(9),GETDATE(),6)
PRINT ‘6) HERE IS DD MON YYYY HH:MM:SS:MMM(24H) FORMAT ==>’ +
CONVERT(CHAR(24),GETDATE(),113)