转换日期后，只得到日期而不是时间

I am trying to convert date as mm/dd/yy format in Sql Server 2008 but after conversion i have only got formatted date but in my database there also time . my database value is :

我尝试在Sql Server 2008中转换日期为mm/dd/yy格式，但是在转换之后，我只得到了格式化的日期，但是在我的数据库中也有时间。我的数据库值是:

2003-02-28 17:22:43.000
2003-02-28 17:22:43.000
2003-02-28 17:24:26.000
2003-02-28 17:24:26.000
2003-03-24 09:45:25.000
2003-03-24 09:45:25.000
2003-03-24 09:46:36.000
2003-03-24 09:46:36.000
2006-07-20 12:47:31.070
2003-03-27 15:44:53.000
2003-03-27 15:44:53.000
2003-03-27 15:51:56.000
2003-03-27 15:51:56.000
2003-03-27 16:05:03.000

my query for convert date format is

我对转换日期格式的查询是

SELECT CONVERT(VARCHAR(20), StatusDate, 101) AS Post_Converted,StatusDesc, RowVersionInitials from PurchaseOrderStatusHistory

and my output is

我的输出是

date is successfully formatted but the 'time' is missing . please suggest me how would i get the database time also .

日期已成功格式化，但“时间”已缺失。请建议我如何获得数据库时间。

2 个解决方案

#1

For some reason, SQL Server doesn't offer mm/dd/yyyy with the time component. I would advise you to use format 120 -- the ISO standard format -- but you are asking specifically for this.

由于某些原因，SQL Server不提供mm/dd/yyyy的时间组件。我建议您使用格式120——ISO标准格式——但是您是特别要求的。

You can concatenate the values together:

您可以将这些值连接在一起:

SELECT CONVERT(VARCHAR(20), StatusDate, 101) + ' ' + CONVERT(VARCHAR(20), StatusDate, 108) AS Post_Converted,
       StatusDesc, RowVersionInitials 
FROM PurchaseOrderStatusHistory;

Instead of 108, you might want 114:

你可能想要114，而不是108:

SELECT CONVERT(VARCHAR(20), StatusDate, 101) + ' ' + CONVERT(VARCHAR(8), StatusDate, 114) AS Post_Converted,
       StatusDesc, RowVersionInitials 
FROM PurchaseOrderStatusHistory;

This gives you the flexibility of adding milliseconds.

这提供了增加毫秒数的灵活性。

#2

CONVERT accepts a third parameter, in your case 101 to specify the format.

CONVERT接受第三个参数，在您的案例101中，它指定格式。

Find details here

在这里找到细节

What you'd need is - assumably - this

你需要的是——假设是——这个

SELECT CONVERT(VARCHAR(20), StatusDate, 120)

... or another appropriate output format.

…或另一种适当的输出格式。

With SQL Server 2012+ there is FORMAT(), but with v2008 you cannot use this...

对于SQL Server 2012+，有FORMAT()，但是对于v2008，您不能使用这个…

#1