Convert datetime in SQL Server

This query gives you examples of possible datetime convertion formats.

SELECT CURRENT_TIMESTAMP AS [Right now],
 CONVERT(VARCHAR, CURRENT_TIMESTAMP, Style) AS [Formatted text],
 Style AS [Style used],
 'CONVERT(VARCHAR, CURRENT_TIMESTAMP, ' + CAST(Style AS VARCHAR) + ')' AS Example
FROM (
  SELECT 0 as Style UNION ALL
  SELECT 100 UNION ALL
  SELECT 1 UNION ALL
  SELECT 101 UNION ALL
  SELECT 2 UNION ALL
  SELECT 102 UNION ALL
  SELECT 3 UNION ALL
  SELECT 103 UNION ALL
  SELECT 4 UNION ALL
  SELECT 104 UNION ALL
  SELECT 5 UNION ALL
  SELECT 105 UNION ALL
  SELECT 6 UNION ALL
  SELECT 106 UNION ALL
  SELECT 7 UNION ALL
  SELECT 107 UNION ALL
  SELECT 8 UNION ALL
  SELECT 108 UNION ALL
  SELECT 9 UNION ALL
  SELECT 109 UNION ALL
  SELECT 10 UNION ALL
  SELECT 110 UNION ALL
  SELECT 11 UNION ALL
  SELECT 111 UNION ALL
  SELECT 12 UNION ALL
  SELECT 112 UNION ALL
  SELECT 13 UNION ALL
  SELECT 113 UNION ALL
  SELECT 14 UNION ALL
  SELECT 114 UNION ALL
  SELECT 20 UNION ALL
  SELECT 120 UNION ALL
  SELECT 21 UNION ALL
  SELECT 121 UNION ALL
  SELECT 126 UNION ALL
  SELECT 127 UNION ALL
  SELECT 130 UNION ALL
  SELECT 131
 ) AS x

Source