Microsoft SQL Server 2005 cast date/time to string

Question

How can I convert @lastEndTime to a string formated YYYY-MM-DD HH:MM:SS.MS?

DECLARE @lastEndTime datetime

Answer 1

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.

Answer 2

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

Answer 3

CONVERT(nvarchar, @lastEndTime, 121)

Answer 4

DECLARE @lastEndTime datetime set @lastEndTime = getdate()

select convert(varchar,@lastEndTime,121)

For more style http://msdn.microsoft.com/en-us/library/ms187928.aspx