Apply To :
Microsoft SQL Server 2008 R2
1.
Date Format = Mon DD YYYY HH:MI AM (or PM)
Standard = Default
SQL Statement =
----------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------
SELECT CONVERT(VARCHAR(20), GETDATE(), 100) [Mon DD YYYY HH:MI AM (or PM)]
----------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------
Results Messages:
2.
Date Format = MM/DD/YY
Standard = USA
SQL Statement =
----------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------
SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY]
----------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------
Results Messages:
3.
Date Format = MM/DD/YYYY
Standard = USA
SQL Statement =
----------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------
SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]
----------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------
Results Messages:
4.
Date Format = DD-MM-YYYY
Standard = USA
SQL Statement =
----------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------
SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY]
----------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------
Results Messages:
5.
Date Format = DD Mon YYYY
Standard = -
SQL Statement =
----------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------
SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY]
----------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------
Results Messages:
6.
Date Format = Mon DD, YYYY
Standard = -
SQL Statement =
----------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------
SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY]
----------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------
Results Messages:
7.
Date Format = HH:MM:SS
Standard = -
SQL Statement =
----------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------
SELECT CONVERT(VARCHAR(8), GETDATE(), 108) [HH:MM:SS]
----------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------
Results Messages:
8.
Date Format = DD Mon YYYY HH:MM:SS:MMM (24h)
Standard = Europe default + milliseconds
SQL Statement =
----------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------
SELECT CONVERT(VARCHAR(24), GETDATE(), 113) [DD Mon YYYY HH:MM:SS:MMM]
----------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------
Results Messages:
9.
Date Format = DD-Mon-YYYY
SQL Statement =
----------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------
SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ' ', '-') AS [DD-Mon-YYYY]
----------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------
Results Messages:
10.
Date Format = DD-Mon-YY
SQL Statement =
----------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------
SELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ' ', '-') AS [DD-Mon-YY]
----------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------
Results Messages:
Source:
http://www.sql-server-helper.com/tips/date-formats.aspx