开发者

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

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜