SQL Server: How to get current date time in YYYYMMDDHHMISSMSS
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:37开发者_StackOverflowPM
So how can I get the current date time in my required format? Pls help.
select replace(
replace(
replace(
replace(convert(varchar(23), getdate(), 121),
'-',''),
'.',''),
' ',''),
':','')
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),':','')
精彩评论