[sql-server-2008-r2] How to Convert datetime value to yyyymmddhhmmss in SQL server?

How to convert datetime value to yyyymmddhhmmss?

for example From 2014-04-17 13:55:12

To 20140417135512

SELECT REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(19), CONVERT(DATETIME, getdate(), 112), 126), '-', ''), 'T', ''), ':', '') 

This query is to convert the DateTimeOffset into the format yyyyMMddhhss with Offset. I have replaced the hyphens, colon(:), period(.) from the data, and kept the hyphen for the seperation of Offset from the DateTime.

SELECT REPLACE(SUBSTRING(CONVERT(VARCHAR(33),SYSDATETIMEOFFSET(),126), 1, 8), '-', '') +
SUBSTRING(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(33), SYSDATETIMEOFFSET(), 126),'T',''),'.',''),':',''),9,DATALENGTH(CONVERT(VARCHAR(33), SYSDATETIMEOFFSET(), 126)))

20090320093349

SELECT CONVERT(VARCHAR,@date,112) + 
LEFT(REPLACE(CONVERT(VARCHAR,@date,114),':',''),6)

This seems to work:

declare @d datetime
set @d = '2014-04-17 13:55:12'

select replace(convert(varchar(8), @d, 112)+convert(varchar(8), @d, 114), ':','') 

Result:

20140417135512

also this works too

SELECT replace(replace(replace(convert(varchar, getdate(), 120),':',''),'-',''),' ','')

Another option I have googled, but contains several replace ...

SELECT REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(19), CONVERT(DATETIME, getdate(), 112), 126), '-', ''), 'T', ''), ':', '') 

FORMAT() is slower than CONVERT(). This answer is slightly better than @jpx's answer because it only does a replace on the time part of the date.

112 = yyyymmdd - no format change needed

108 = hh:mm:ss - remove :

SELECT CONVERT(VARCHAR, GETDATE(), 112) + 
       REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ':', '')