Microsoft SQL Server 2005 cast date/time to string
How can I convert @lastEndTime to a string开发者_高级运维 formated YYYY-MM-DD HH:MM:SS.MS
?
DECLARE @lastEndTime datetime
Check the MSDN Books Online documentation for CAST and CONVERT - it has a complete list of all supported, built-in date formats that you can use with CONVERT.
E.g.
CONVERT(VARCHAR(50), GETDATE(), 100)
will convert today's date and time to a string in the format mon dd yyyy hh:miAM (or PM)
.
If your string does not match any of those formats, then you either have to
- use
DATEPART
function to extract bits and pieces of your DATETIME and concatenate that together manually - use SQLCLR and the .NET DateTime function to do it in a SQL-CLR user-defined function
- don't do it in SQL Server and pass back the DATETIME to the calling client and let the caller handle the conversion to the actual presentation format
Really horribly, to get a precise format you have to use the datepart function and build it up.
select datepart(yyyy, @lastEndTime) + '-' + datepart(mm, @lastEndTime) + '-' + datepart(dd, @lastEndTime) +' ' + datepart(hh, @lastEndTime) + ':' + datepart(mm, @lastEndTime) + ':' + datepart(ss, @lastEndTime) + '.' + datepart(ms,@lastEndTime)
You could define it as a function for ease of use though.
Edit - as someone has pointed out, this format happens to be a standard - ODBC canonical so
CONVERT(CHAR(23), @lastEndTime, 121)
should do it.
CONVERT(nvarchar, @lastEndTime, 121)
DECLARE @lastEndTime datetime set @lastEndTime = getdate()
select convert(varchar,@lastEndTime,121)
For more style http://msdn.microsoft.com/en-us/library/ms187928.aspx
精彩评论