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.

Dewaynedewberry

Answer 1

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

Larina

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.

Ergotism

+1 - very helpful. Now you can use all these datetime.tostring formats from the .net framework. msdn.microsoft.com/en-us/library/zdtaw1bw(v=vs.110).aspx – Bersagliere

Thanks, Worked for me! – Guinna

Answer 3

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.

Pedro

Answer 4

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.

Catalinacatalo

Answer 5

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()));

Faille

Answer 6

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.

Bersagliere

Answer 7

select CONVERT(nvarchar(8),getdate(),112) + 
case when Len(CONVERT(nvarchar(2),DATEPART(HH,getdate()))) =1 then '0' + CONVERT(nvarchar(2),DATEPART(HH,getdate())) else CONVERT(nvarchar(2),DATEPART(HH,getdate())) end +
case when Len( CONVERT(nvarchar(2),DATEPART(MI,getdate())) ) =1 then '0' + CONVERT(nvarchar(2),DATEPART(MI,getdate())) else CONVERT(nvarchar(2),DATEPART(MI,getdate())) end

Caty