How to convert datetime value to yyyymmddhhmmss?
for example
From 2014-04-17 13:55:12
To 20140417135512
-
May this SO Answer will help you : stackoverflow.com/questions/19506225/…ULLAS MOHAN.V– ULLAS MOHAN.V2014-04-17 13:09:17 +00:00Commented Apr 17, 2014 at 13:09
Add a comment
|
8 Answers
Since SQL Server Version 2012 you can use:
SELECT format(getdate(),'yyyyMMddHHmmssffff')
1 Comment
RonJohn
This simplifies my life so much!
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
Comments
SELECT REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(19), CONVERT(DATETIME, getdate(), 112), 126), '-', ''), 'T', ''), ':', '')
Comments
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.
Format 112 = yyyymmdd - no format change needed
Format 108 = hh:mm:ss - so replace is used to remove the :
SELECT CONVERT(VARCHAR, GETDATE(), 112) +
REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ':', '')
Comments
Another option I have googled, but contains several replace ...
SELECT REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(19), CONVERT(DATETIME, getdate(), 112), 126), '-', ''), 'T', ''), ':', '')
Comments
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)))
Comments
also this works too
SELECT replace(replace(replace(convert(varchar, getdate(), 120),':',''),'-',''),' ','')
Comments
20090320093349
SELECT CONVERT(VARCHAR,@date,112) +
LEFT(REPLACE(CONVERT(VARCHAR,@date,114),':',''),6)
1 Comment
Nino Filiu
Code-only answers are considered low-quality. Your answer is likely to be deleted if you don't edit it so as to add an explanation.