如何转换字段以在SQL中显示日期和时间

I have column that looks like this:

我的列看起来像这样:

Jan  8 2013  2:47PM

but I want to convert and show something like this:

但我想转换并显示如下内容:

01/08/2013 2:47 PM

I have tried something like this but it does not show the time:

我尝试过类似的东西,但没有显示时间:

select convert (date, Date,101)as MyDate

3 个解决方案

#1

if your original field is a varchar: you can do something like this:

如果您的原始字段是varchar:您可以执行以下操作:

select CONVERT(varchar(10), CAST('Jan  8 2013  2:47PM' AS DATETIME), 101) + ' ' + RIGHT(CONVERT(VARCHAR, 'Jan  8 2013  2:47PM', 100), 7)

Just replace the string with your field.

只需用字段替换字符串即可。

#2

You can try something like this:-

你可以尝试这样的事情: -

   Convert(datetime, '01/08/2013', 103)

#3

SQL Server does not provide a way of doing this using a single CONVERT statement, but you can use the following to achieve the objective:

SQL Server没有提供使用单个CONVERT语句执行此操作的方法,但您可以使用以下方法来实现此目标:

SELECT CONVERT(VARCHAR, date, 101) + RIGHT(CONVERT(VARCHAR, date, 100), 8)