SQL 将日期时间格式转换为 12 小时

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/9177799/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-01 14:18:17  来源:igfitidea点击:

Converting datetime format to 12 hour

sqlsql-serversql-server-2005

提问by dansasu11

I have this query

我有这个查询

select CONVERT(varchar(5), tdate ,108) AS [Time] from table 

which gives me the time in 24 hour format( military)

这给了我 24 小时格式的时间(军事)

I wanted to convert it into a 12 hour format so i tried the query below

我想将其转换为 12 小时格式,所以我尝试了下面的查询

 select SUBSTRING(CONVERT(VARCHAR, tdate, 100),13,2) + ':' 
+ SUBSTRING(CONVERT(VARCHAR, tdate, 100),16,2) + ''
+ SUBSTRING(CONVERT(VARCHAR, tdate, 100),18,2) AS T  
from table

and i get the 12 hour format but I am just curious if there is a shorter or better way of doing it. any help?

我得到了 12 小时的格式,但我很好奇是否有更短或更好的方法。有什么帮助吗?

回答by Gaspa79

If you want to convert the current datetime for example:

例如,如果要转换当前日期时间:

SELECT CONVERT(VARCHAR, getdate(), 100) AS DateTime_In_12h_Format

Instead of getdate() you can put your desired column in a query (such as tdate in your example). If you want JUST the time in 12h and not the date and time use substring/right to separate them. It seems that you already know how to =).

您可以将所需的列放在查询中(例如示例中的 tdate),而不是 getdate()。如果您只想要 12 小时的时间而不是日期和时间,请使用 substring/right 将它们分开。看来你已经知道如何=)。

This pagelists every datetime conversion. It's really handy if you need other types of conversions.

此页面列出了每个日期时间转换。如果您需要其他类型的转换,这真的很方便。

回答by Taryn

This will return just the time, not the date.

这将只返回时间,而不是日期。

SELECT RIGHT(CONVERT(VARCHAR, getdate(), 100), 7) AS time

For your table data:

对于您的表数据:

select RIGHT(CONVERT(varchar, tdate ,100), 7) AS [Time] from table 

回答by mirzad ahamed

Below code will return only time like 10:30 PM

下面的代码将只返回像 10:30 PM 这样的时间

SELECT FORMAT(CAST(getdate() AS DATETIME),'hh:mm tt') AS [Time]  

回答by Luis Gerardo López Salazar

Get date of server

获取服务器日期

SELECT LTRIM(RIGHT(CONVERT(VARCHAR(20), GETDATE(), 100), 7))

or

或者

If it is stored in the table

如果存储在表中

SELECT LTRIM(RIGHT(CONVERT(VARCHAR(20), datename, 100), 7))

Result:

结果:

11:41AM

上午 11:41

回答by Arjun S V

ifnull(date_format(at.date_time,'%d/%m/%Y'),"") AS date_time,
ifnull(time_format(at.date_time ,'%h:%i:%s'),"") AS date_time

This is how a SQL procedure looks...(for separating date and time)..there is no need of a special column for time/date....

这就是 SQL 过程的样子......(用于分隔日期和时间)......不需要时间/日期的特殊列......

Note:if H instead of h it will show the "hour in 24 hour" format

注意:如果 H 而不是 h 它将显示“24 小时制小时”格式