2018-01-29

Formatting Dates with CONVERT

Dave Mason - SQL Server CONVERT

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!

StyleOutput Format
100Jan 26 2018 8:50PM
10101/26/2018
1022018.01.26
10326/01/2018
10426.01.2018
10526-01-2018
10626 Jan 2018
107Jan 26, 2018
10820:50:13
109Jan 26 2018 8:50:13:970PM
11001-26-2018
1112018/01/26
11220180126
11326 Jan 2018 20:50:13:970
11420:50:13:970
1202018-01-26 20:50:13
1212018-01-26 20:50:13.970
1262018-01-26T20:50:13.970
1272018-01-26T20:50:13.970
13010 ????? ?????? 1439 8:50:13:
13110/05/1439 8:50:13:970PM

SHARE

No comments :

Post a Comment