SQL SERVER – Date Formats using CONVERT() Function

In the Previous article, we learned about the Convert() function,  which can be used to change the date format in what ever format we like. Now, In this article we will see How to get various date formats  using Convert() function.

One of the Most frequently asked questions in any of the Interview or in any of the SQL Server forums is - How to extract only Date value from DATETIME column or How to change the DATETIME value in to a specified format. The answer to all these queries is by using the Convert() function. The CONVERT function's third optional parameter  ‘style’ plays a vital role in formatting the value while  converting the  a datetime value to a VARCHAR.

Let us see various Standard Date Formats that are available in SQL Server.

SQL statement	Output
SELECT CONVERT(VARCHAR(8), GETDATE(), 1)  'MM/DD/YY'
SELECT CONVERT (VARCHAR(8), GETDATE(), 2)  'YY.MM.DD'
SELECT CONVERT (VARCHAR(8), GETDATE(), 3) 'DD/MM/YY'
SELECT CONVERT (VARCHAR(8), GETDATE(), 4) 'DD.MM.YY'
SELECT CONVERT (VARCHAR(8), GETDATE(), 5) 'DD-MM-YY'
SELECT CONVERT (VARCHAR(9), GETDATE(), 6)  'DD MON YY'
SELECT CONVERT (VARCHAR(10), GETDATE(), 7)  'Mon DD, YY'
SELECT CONVERT (VARCHAR(8), GETDATE(), 8)  'HH:MM:SS'
SELECT CONVERT (VARCHAR(26), GETDATE(), 9)       'Mon DD YYYY HH:MM:SS:MMMAM/PM'
SELECT CONVERT (VARCHAR(8),  GETDATE(), 10)    'MM-DD-YY'
SELECT CONVERT (VARCHAR(8),  GETDATE(), 11) 'YY/MM/DD'
SELECT CONVERT (VARCHAR(6), GETDATE(), 12)  'YYMMDD'
SELECT CONVERT (VARCHAR(24), GETDATE(), 13)       'DD Mon YYYY HH:MM:SS:MMM'
SELECT CONVERT (VARCHAR(12), GETDATE(), 14)  'HH:MM:SS:MMM'
SELECT CONVERT (VARCHAR(19), GETDATE(), 20)  'YYYY-MM-DD HH:MM:SS'
SELECT CONVERT (VARCHAR(35), GETDATE(), 21)   'YYYY-MM-DD HH:MM:SS.MMM'
SELECT CONVERT (VARCHAR(20), GETDATE(), 22)  'MM/DD/YY HH:MM:SS PM(AM)'
SELECT CONVERT (VARCHAR(10), GETDATE(), 23)  'YYYY-MM-DD'
SELECT CONVERT (VARCHAR(10), GETDATE(), 24)  'HH:MM:SS'
SELECT CONVERT (VARCHAR(10), GETDATE(), 25) 'YYYY-MM-DD'
SELECT CONVERT (VARCHAR(30), GETDATE(),100)  'Mon DD YYYY HH:MMAM/PM'
SELECT CONVERT (VARCHAR(10), GETDATE(), 101)  'MM/DD/YYYY'
SELECT CONVERT (VARCHAR(10), GETDATE(), 102)  'YYYY.MM.DD'
SELECT CONVERT (VARCHAR(10), GETDATE(), 103)  'DD/MM/YYYY'
SELECT CONVERT (VARCHAR(10), GETDATE(), 104) 'DD.MM.YYYY'
SELECT CONVERT (VARCHAR(10), GETDATE(), 105) 'DD-MM-YYYY'
SELECT CONVERT (VARCHAR(11), GETDATE(), 106) 'DD Mon YYYY'
SELECT CONVERT (VARCHAR(12), GETDATE(), 107)  'Mon DD, YYYY'
SELECT CONVERT (VARCHAR(8), GETDATE(), 108)  'HH:MM:SS'
SELECT CONVERT (VARCHAR(11), GETDATE(), 109) 'Mon DD YYYY'
SELECT CONVERT (VARCHAR(10), GETDATE(), 110)  'MM-DD-YYYY'
SELECT CONVERT (VARCHAR(10), GETDATE(), 111) 'YYYY/MM/DD'
SELECT CONVERT (VARCHAR(10), GETDATE(), 112) 'YYYYMMDD'
SELECT CONVERT (VARCHAR(24), GETDATE(), 113)  'DD Mon YYYY HH:MM:SS:MMM'
SELECT CONVERT (VARCHAR(12), GETDATE(), 114) 'HH:MM:SS:MMM'
SELECT CONVERT (VARCHAR(19), GETDATE(), 120) 'YYYY-MM-DD HH:MM:SS'
SELECT CONVERT (VARCHAR(23), GETDATE(), 121) 'YYYY-MM-DD HH:MM:SS.MMM'
SELECT CONVERT (VARCHAR(24), GETDATE(), 126) 'YYYY-MM-DDTHH:MM:SS.MMM'
SELECT CONVERT (VARCHAR(110), GETDATE(), 127)   'YYYY-MM-DDTHH:MM:SS.MMM'
SELECT CONVERT (VARCHAR(26), GETDATE(), 130)  'DD Mon YYYY HH:MM:SS:MMMAM/PM'
SELECT CONVERT (VARCHAR(25), GETDATE(), 131)  'DD/MM/YYYY HH:MM:SS:MMMAM/PM'