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(),开发者_JAVA技巧112)
but that is as far as I got.
Thanks.
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.
select replace(
replace(
replace(convert(varchar(19), getdate(), 126),
'-',''),
'T',''),
':','')
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.
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.
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()));
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.
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
精彩评论