SQL: Date Format

The Following syntax are helpful to get desired date formats in SQL.

Syntax for CAST:
CAST ( expression AS data_type [ (length ) ])

Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Example: DD/MM/YYYY
select convert(varchar(16), getdate(), 103)

MM/DD/YYYY
select convert(varchar(16), getdate(), 101)

Without century (yy) (1) With century (yyyy) Standard Input/Output (3)
- 0 or 100 (1, 2) Default mon dd yyyy hh:miAM (or PM)

1 101 U.S. mm/dd/yyyy
2 102 ANSI yy.mm.dd
3 103 British/French dd/mm/yy
4 104 German dd.mm.yy
5 105 Italian dd-mm-yy
6 106 (1) - dd mon yy
7 107 (1) - Mon dd, yy
8 108 - hh:mi:ss
- 9 or 109 (1, 2) Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM)
10 110 USA mm-dd-yy
11 111 JAPAN yy/mm/dd
12 112 ISO yymmdd
- 13 or 113 (1, 2) Europe default + milliseconds dd mon yyyy hh:mi:ss:mmm(24h)
14 114 - hh:mi:ss:mmm(24h)
- 20 or 120 (2) ODBC canonical yyyy-mm-dd hh:mi:ss(24h)
- 21 or 121 (2) ODBC canonical (with milliseconds) yyyy-mm-dd hh:mi:ss.mmm(24h)
- 126 (4) ISO8601 yyyy-mm-ddThh:mi:ss.mmm (no spaces)
127(6, 7) ISO8601 with time zone Z. yyyy-mm-ddThh:mi:ss.mmmZ
(no spaces)
- 130 (1, 2) Hijri (5) dd mon yyyy hh:mi:ss:mmmAM
- 131 (2) Hijri (5) dd/mm/yy hh:mi:ss:mmmAM

More Examples:

SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY]
SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]
SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY]
SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY]
SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY]
SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY]
SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY]
SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY]
SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY]
SELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY]
SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY]