Have Geek Brain

Microsoft SQL Server 2005 date / time date for string

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

?

DECLARE @lastEndTime datetime
+2


a source to share


4 answers 


CONVERT(nvarchar, @lastEndTime, 121)


+2


a source


Indeed, to get the exact format, you should use the datepart function and create it.

select datepart(yyyy, @lastEndTime) + '-' + datepart(mm, @lastEndTime) + '-' + datepart(dd, @lastEndTime) +' ' + datepart(hh, @lastEndTime) + ':' + datepart(mm, @lastEndTime) + ':' + datepart(ss, @lastEndTime) + '.' + datepart(ms,@lastEndTime)

You can define it as a function for ease of use.



Edit - as someone pointed out, this format is standard - canonical ODBC, so

 CONVERT(CHAR(23), @lastEndTime, 121)

should do it.

+3


a source


Check out the Books Online documentation for MSDN for CAST and CONVERT - it has a complete list of all supported built-in date formats that you can use with CONVERT.

eg.

 CONVERT(VARCHAR(50), GETDATE(), 100)

converts today's date and time to a string in the format mon dd yyyy hh:miAM (or PM)

.

If your string doesn't match any of these formats, you need to either

  • use a DATEPART

    function to extract the bits and parts of your DATETIME and manually concatenate them
  • use SQLCLR and .NET DateTime function to execute this function in a custom SQL-CLR function.
  • do not do this in SQL Server and pass the DATETIME back to the calling client and let the calling agent handle the conversion to the actual presentation format
+3


a source


DECLARE @lastEndTime datetime set @lastEndTime = getdate ()

select convert (varchar, @lastEndTime, 121)

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

+1


a source






More articles:


All Articles