how to format getdate into YYYYMMDDHHmmSS

Question

In SQL Server how do I format getdate() output into YYYYMMDDHHmmSS where HH is 24 hour format?

I've got the YYYYMMDD done with

select CONVERT(varchar,GETDATE(),112)

but that is as far as I got.

Thanks.

Answer 1

Try this:

select CONVERT(varchar, GETDATE(), 120) e.g.

2011-09-23 12:18:24 (yyyy-mm-dd hh:mi:ss (24h) ,ODBC canonical).

Hth.

Answer 2

Just for anyone searching for this functionality that has SQL Server 2012 you can use the FORMAT function:

SELECT FORMAT ( GETDATE(), 'yyyyMMddHHmmss') AS 'Custom DateTime'

This allows any .NET format strings making it a useful new addition.

Answer 3

converting datetime that way requires more than one call to convert. Best use for this is in a function that returns a varchar.

select CONVERT(varchar,GETDATE(),112) --YYYYMMDD
select CONVERT(varchar,GETDATE(),108) --HH:MM:SS

Put them together like so inside the function

DECLARE @result as varchar(20)
set @result = CONVERT(varchar,GETDATE(),112) + ' ' + CONVERT(varchar,GETDATE(),108)
print @result

20131220 13:15:50

As Thinhbk posted you can use select CONVERT(varchar,getdate(),20) or select CONVERT(varchar,getdate(),120) to get quite close to what you want.

Answer 4

Another option!

SELECT CONVERT(nvarchar(8), GETDATE(),112) + 
   CONVERT(nvarchar(2),DATEPART(HH,GETDATE())) + 
   CONVERT(nvarchar(2),DATEPART(MI,GETDATE())) + 
   CONVERT(nvarchar(2),DATEPART(SS,GETDATE()));

Answer 5

select replace(
       replace(
       replace(convert(varchar(19), getdate(), 126),
       '-',''),
       'T',''),
       ':','')

Answer 6

Close but not exactly what you are asking for:

select CONVERT(varchar, GETDATE(), 126)

e.g.

2011-09-23T12:18:24.837

(yyyy-mm-ddThh:mi:ss.mmm (no spaces), ISO8601 without timezone)

Ref: CAST and CONVERT

There is no way to specify a custom format with CONVERT(). The other option is to perform string manipulation to create in the format you desire.