SQL Server: How to get current date time in YYYYMMDDHHMISSMSS

Question

I need the current date time in the format YYYYMMDDHHMISSMIS

Example:

20110723233747607

By using the CURRENT_TIMESTAMP or getdate() functions, we can retrieve the current datetime into 2011-07-23 23:37:47.607 format. If I use REPLACE and CONVERT functions to remove the "-" and ":" characters, then I get the value into

Jul 23 2011 11:37PM

...format. But I need the current date time as 20110723233747607 to use it for my another purpose.

My SQL query is:

SELECT REPLACE(CONVERT(VARCHAR(20), CURRENT_TIMESTAMP),'.','')

output: Jul 23 2011 11:37PM

So how can I get the current date time in my required format? Pls help.

Answer 1

select replace(
       replace(
       replace(
       replace(convert(varchar(23), getdate(), 121),
       '-',''),
       '.',''),
       ' ',''),
       ':','')

Answer 2

I do not know why you need to use so many REPLACE() functions. Usage of functions really reduce the execution time. I used two CONVERT and one REPLACE function below.

SELECT CONVERT(VARCHAR(8), GETDATE(), 112) + REPLACE(CONVERT(VARCHAR(12), GETDATE(), 114),':','')