SELECT convert(char, getdate(), 102) as '102, ANSI, yyyy.mm.dd'
, convert(char, getdate(), 108) as '108, -, hh:mi:ss'
, convert(char, getdate(), 112) as '112, ISO, yyyymmdd'
, convert(char, getdate(), 114) as '114, -, hh:mi:ss:mmm(24h)'
, convert(char, getdate(), 120) as '120, ODBC canonical, yyyy-mm-dd hh:mi:ss(24h)'
, convert(char, getdate(), 121) as '121, , ODBC canonical, yyyy-mm-dd hh:mi:ss.mmm(24h)'

102, ANSI, yyyy.mm.dd          108, -, hh:mi:ss               112, ISO, yyyymmdd             114, -, hh:mi:ss:mmm(24h)      120, ODBC canonical, yyyy-mm-dd hh:mi:ss(24h) 121, , ODBC canonical, yyyy-mm-dd hh:mi:ss.mmm(24h)
------------------------------ ------------------------------ ------------------------------ ------------------------------ --------------------------------------------- ---------------------------------------------------
2007.09.15                     01:57:19                       20070915                       01:57:19:447                   2007-09-15 01:57:19                           2007-09-15 01:57:19.447       

(1 row(s) affected)