Convert Date to String in SQL Server

Whenever I see a question on converting a Date to String in SQL Server, I see developers nesting various REPLACE functions to get the desired output. However with some knowledge of formatting date and time, this requirement can be achieved in a simple manner as shown below.

Here’s a query that converts a Date to a String in SQL Server:

DECLARE @Dt as DateTime
SET @Dt = '2010-04-27 11:30:17'

SELECT CONVERT(CHAR(8), @Dt, 112)
+ REPLACE(CONVERT(CHAR(8), @Dt, 114), ':', '')

In the query shown above, the style value 112 gives an output of yymmdd and a style value 114, gives an output of hh:mi:ss:mmm(24h). To display the milliseconds too, change Char(8) to Char(12).

OUTPUT

Further Reading: Cast and Convert