使用sqlserver将数据时间转换为其他格式 [英] Convert data time to a different format using sqlserver

问题描述

我知道将日期时间转换为所需格式的基本知识但我处于某种情况

将datetime转换为varchar in格式不同。



如果我输入日期为 2015-02-27 19：31：30.623



我应该按以下格式得到我想要的输出。



02 / 27/2015 7:31 PM



我希望我能尽快得到答案。

Hi,

I know basics of converting datetime to required format but I am in a situation
to convert datetime to varchar in a different format.

If I Enter date as 2015-02-27 19:31:30.623

I should get my desired output in the following format.

02/27/2015 7:31 PM

I hope I would get an answer as soon as possible.

推荐答案

是的，格式应该留给你的应用程序中的表示层，但如果你想在sql查询中以不同的方式显示它，你可以使用CONVERT：

http://www.sql-server-helper.com/tips/date-formats.aspx [ ^ ]



如果你有sql 2012及以上，你可以我们e格式

https：// msdn。 microsoft.com/en-us/library/hh213505%28v=sql.110%29.aspx [ ^ ]

yes, the formatting should be left up to the presentation layer in your app, but if you want to display it differently in a sql query you could use CONVERT:
http://www.sql-server-helper.com/tips/date-formats.aspx[^]

if you have sql 2012 and above, you could use FORMAT
https://msdn.microsoft.com/en-us/library/hh213505%28v=sql.110%29.aspx[^]

请参见：http://www.w3schools.com/sql/func_convert.asp [ ^ ]标准SQL日期转换代码：如果它不是其中之一（并且不是）那么你将不得不使用SUBSTRING或DATEPART函数来获得你想要的东西。



但是如前所述，你不应该在SQL中这样做：它真的是一个演示函数并且应该尽可能晚地完成，以确保它可以用户理解的格式完成。

02/27/2015很好 - 可以看作是d很容易吃。

02/03/2015不是 - 在欧洲是3月2日，在美国是2月3日。



你不应该假设输出格式，除非它是一个非常具体的报告，必须是那样。

See here: http://www.w3schools.com/sql/func_convert.asp[^] for the standard SQL date convert codes: if it isn;t one of those (and it isn't) then you will have to play with the SUBSTRING or DATEPART functions to get exactly what you want.

But as has been mentioned, you shouldn't do this in SQL: it's really a presentation function and should be done as late as possible to ensure that it can be done in the format the user understands.
02/27/2015 is fine - it can be seen as the date easily.
02/03/2015 isn't - in Europe that's the 2nd of March, in the US it's the 3rd of February.

You should not assume an output format unless it's for a very specific report where it has to be that way.

你好Kumar，



我认为这个解决方案可以帮助你获得所需的

输出

------------------ -----------------

选择转换（Varchar（10），演员（'2015-02-27'作为日期时间），101）+' '+转换（Varchar（15），演员（'2015-02-27 19：31：30.623'作为时间），100）

Hi Kumar,

I think this solution might help you for your desired
output
-----------------------------------
Select Convert(Varchar(10),Cast('2015-02-27' as datetime),101) + ' ' + Convert(Varchar(15),Cast('2015-02-27 19:31:30.623' as TIME),100)

这篇关于使用sqlserver将数据时间转换为其他格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！