Formatting Dates with CONVERT
- Posted in:
- Development
- SQL Server
- SSMS
- T-SQL

Displaying dates and times with different formats in TSQL is a task I run into quite a bit. I used to visit this page so many times, I'm surprised it doesn't have a "Welcome back, Dave!" banner on it at the top. After umpteen million times, I decided it was time to be more efficient. I created this query that's come in handy numerous times. I considered dumping it into a view, but I've found it's nice to copy/paste the CONVERT statement (directly from a script) and replace CURRENT_TIMESTAMP with whatever column I want to have formatted.
/********************************************************************
Convert DATETIME
********************************************************************/
SELECT 100 Style, CONVERT(VARCHAR, CURRENT_TIMESTAMP, 100) [Output Format] UNION
SELECT 101 Style, CONVERT(VARCHAR, CURRENT_TIMESTAMP, 101) UNION
SELECT 102 Style, CONVERT(VARCHAR, CURRENT_TIMESTAMP, 102) UNION
SELECT 103 Style, CONVERT(VARCHAR, CURRENT_TIMESTAMP, 103) UNION
SELECT 104 Style, CONVERT(VARCHAR, CURRENT_TIMESTAMP, 104) UNION
SELECT 105 Style, CONVERT(VARCHAR, CURRENT_TIMESTAMP, 105) UNION
SELECT 106 Style, CONVERT(VARCHAR, CURRENT_TIMESTAMP, 106) UNION
SELECT 107 Style, CONVERT(VARCHAR, CURRENT_TIMESTAMP, 107) UNION
SELECT 108 Style, CONVERT(VARCHAR, CURRENT_TIMESTAMP, 108) UNION
SELECT 109 Style, CONVERT(VARCHAR, CURRENT_TIMESTAMP, 109) UNION
SELECT 110 Style, CONVERT(VARCHAR, CURRENT_TIMESTAMP, 110) UNION
SELECT 111 Style, CONVERT(VARCHAR, CURRENT_TIMESTAMP, 111) UNION
SELECT 112 Style, CONVERT(VARCHAR, CURRENT_TIMESTAMP, 112) UNION
SELECT 113 Style, CONVERT(VARCHAR, CURRENT_TIMESTAMP, 113) UNION
SELECT 114 Style, CONVERT(VARCHAR, CURRENT_TIMESTAMP, 114) UNION
SELECT 120 Style, CONVERT(VARCHAR, CURRENT_TIMESTAMP, 120) UNION
SELECT 121 Style, CONVERT(VARCHAR, CURRENT_TIMESTAMP, 121) UNION
SELECT 126 Style, CONVERT(VARCHAR, CURRENT_TIMESTAMP, 126) UNION
SELECT 127 Style, CONVERT(VARCHAR, CURRENT_TIMESTAMP, 127) UNION
SELECT 130 Style, CONVERT(VARCHAR, CURRENT_TIMESTAMP, 130) UNION
SELECT 131 Style, CONVERT(VARCHAR, CURRENT_TIMESTAMP, 131);
Here's what the output looks like. This will vary, based on language environment setting (us_english is shown here), and of course, the current date and time. Yeah, I know...You should leave date & time formatting to the application or presentation layer. But some of us live in SSMS almost all day. Enjoy!
Style | Output Format |
---|---|
100 | Jan 26 2018 8:50PM |
101 | 01/26/2018 |
102 | 2018.01.26 |
103 | 26/01/2018 |
104 | 26.01.2018 |
105 | 26-01-2018 |
106 | 26 Jan 2018 |
107 | Jan 26, 2018 |
108 | 20:50:13 |
109 | Jan 26 2018 8:50:13:970PM |
110 | 01-26-2018 |
111 | 2018/01/26 |
112 | 20180126 |
113 | 26 Jan 2018 20:50:13:970 |
114 | 20:50:13:970 |
120 | 2018-01-26 20:50:13 |
121 | 2018-01-26 20:50:13.970 |
126 | 2018-01-26T20:50:13.970 |
127 | 2018-01-26T20:50:13.970 |
130 | 10 ????? ?????? 1439 8:50:13: |
131 | 10/05/1439 8:50:13:970PM |
Comments