16

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Wayne
  • 914
  • 2
  • 13
  • 25

7 Answers7

31

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.

Richard
  • 1,124
  • 10
  • 13
21
select replace(
       replace(
       replace(convert(varchar(19), getdate(), 126),
       '-',''),
       'T',''),
       ':','')
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
7

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.

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
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.

Thinhbk
  • 2,194
  • 1
  • 23
  • 34
2

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()));
Han
  • 449
  • 5
  • 18
0

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.

TWood
  • 2,563
  • 8
  • 36
  • 58
-1
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
L. F.
  • 19,445
  • 8
  • 48
  • 82
Soth Sopheak Trea
  • 1