Date Formats in SQL Server
In this post, you’ll learn how to use Convert function in SQL queries to display various SQL Server Date Time formats.
There are many times when the date and time doesn’t show like that way you wanted to display in your application. You will need to format the data in the application or use the built-in SQL Server functions to format the date time like you want.
Date Formats in SQL Server
To get the various date time formats, we can perform the following
- Use the CONVERT function and the date format option.
- Eg : To get the date format in YYYY-MM-DD , you will need to use the code like this : SELECT CONVERT(varchar(10), getdate(), 23) where parameter 1 is the data type that is returned , 2nd parameter the date that you want to be formatted and the third parameter is the date format option.
Below is collection of all possible Standard Date Time Formats in SQL Server.
| Date Format | SQL Statement | Sample Output |
| MM/DD/YY | SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY] | 02/22/21 |
| MM/DD/YYYY | SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY] | 02/22/2021 |
| YY.MM.DD | SELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD] | 21.02.22 |
| YYYY.MM.DD | SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD] | 2021.02.22 |
| DD/MM/YY | SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY] | 22/02/21 |
| DD/MM/YYYY | SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY] | 22/02/2021 |
| DD.MM.YY | SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY] | 22.02.21 |
| DD.MM.YYYY | SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY] | 22.02.2021 |
| DD-MM-YY | SELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY] | 22-02-21 |
| DD-MM-YYYY | SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY] | 22-02-2021 |
| DD Mon YY | SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY] | 22 Feb 21 |
| DD Mon YYYY | SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY] | 22 Feb 2021 |
| Mon DD, YY | SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY] | Feb 22, 21 |
| Mon DD, YYYY | SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY] | Feb 22, 2021 |
| MM-DD-YYYY | SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY] | 02-22-2021 |
| YY/MM/DD | SELECT CONVERT(VARCHAR(8), GETDATE(), 11) AS [YY/MM/DD] | 21/02/22 |
| YYYY/MM/DD | SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD] | 2021/02/22 |
| YYMMDD | SELECT CONVERT(VARCHAR(6), GETDATE(), 12) AS [YYMMDD] | 210222 |
| YYYYMMDD | SELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD] | 20210222 |
| Mon DD YYYY HH:MIAM (or PM) | SELECT CONVERT(VARCHAR(20), GETDATE(), 100) | Feb 22 2021 10:27PM |
| DD Mon YYYY HH:MM:SS:MMM(24h) | SELECT CONVERT(VARCHAR(24), GETDATE(), 113) | 22 Feb 2021 22:27:22:440 |
| HH:MI:SS:MMM(24H) | SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS:MMM(24H)] | 22:27:36:007 |
| YYYY-MM-DD HH:MI:SS(24h) | SELECT CONVERT(VARCHAR(19), GETDATE(), 120) | 2021-02-22 22:27:49 |
| YYYY-MM-DD HH:MI:SS.MMM(24h) | SELECT CONVERT(VARCHAR(23), GETDATE(), 121) | 2021-02-22 22:27:58.730 |
| YYYY-MM-DDTHH:MM:SS:MMM | SELECT CONVERT(VARCHAR(23), GETDATE(), 126) | 2021-02-22T22:28:07.613 |
Tag:Data Types, Date formats, SQL Server
You may also like
What Programming Languages Should I Learn?
25 January, 2023
Printer Friendly Web Pages with Print Friendly
15 January, 2023
How to Minimize all Open Windows quickly in Windows 10?
15 January, 2023