blocks|key|760045|text|SQL+Server+2008：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|760046|SELECT+cast(AttDate+as+time)+[time]
FROM+yourtable|code-block|syntax|javascript|760047|早期版本：|760048|SELECT+convert(char(5),+AttDate,+108)+[time]
FROM+yourtable|760049|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

SQL Server 2008:

<pre><code>SELECT cast(AttDate as time) [time]
FROM yourtable
</code></pre>

Earlier versions:

<pre><code>SELECT convert(char(5), AttDate, 108) [time]
FROM yourtable
</code></pre>

blocks|key|543483|text|假设Sql+server|type|unstyled|depth|inlineStyleRanges|entityRanges|data|543484|SELECT+CONVERT(VARCHAR(8),GETDATE(),108)|offset|length|style|CODE|543485|entityMap^0|0|0|14|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@$D|L|E|M|F|G]]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

Assuming Sql server 

<code>SELECT CONVERT(VARCHAR(8),GETDATE(),108)</code>

blocks|key|543724|text|从datetime获取时间的最简单方法是：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|543725|SELECT+convert(time(0),getDate())|code-block|syntax|javascript|543726|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

The simplest way to get the time from datetime without millisecond stack is:

<pre><code>SELECT convert(time(0),getDate())
</code></pre>

blocks|key|543854|text|试试这个，它会起作用的：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|543855|CONVERT(VARCHAR(8),DATETIME,114)|code-block|syntax|javascript|543856|为您的reference。|offset|length|543857|entityMap|0|LINK|mutability|MUTABLE|url|http://www.sqlines.com/oracle-to-sql-server/to_char_datetime^0|0|0|3|9|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

Try this, it will work:

<pre><code>CONVERT(VARCHAR(8),DATETIME,114)
</code></pre>

For your <a href="http://www.sqlines.com/oracle-to-sql-server/to_char_datetime" rel="noreferrer">reference</a>.

blocks|key|760108|text|试试这个：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|760109|select++convert(nvarchar,CAST(getdate()as+time),100)|code-block|syntax|javascript|760110|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try this:

<pre><code>select convert(nvarchar,CAST(getdate()as time),100)
</code></pre>

blocks|key|760102|text|select+AttDate,convert(char(5),+AttDate,+108)+[Time]+from+yourTableName|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|760103|entityMap^0|0|1Z|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

<code>select AttDate,convert(char(5), AttDate, 108) [Time] from yourTableName</code>

blocks|key|760166|text|我经常使用这个脚本从DateTime获取时间：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|760167|SELECT+CONVERT(VARCHAR(9),RIGHT(YOURCOLUMN_DATETIME,9),108)+FROM+YOURTABLE|code-block|syntax|javascript|760168|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I often use this script to get Time from DateTime:

<pre><code>SELECT CONVERT(VARCHAR(9),RIGHT(YOURCOLUMN_DATETIME,9),108) FROM YOURTABLE
</code></pre>

blocks|key|760364|text|select+cast+(as+time(0))|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|760365|是个很好的条款。例如：|unstyled|760366|(select+cast(start_date+as+time(0)))+AS+'START+TIME'|760367|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|L|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$B|C]]|$1|I|3|-4|5|F|7|N|8|@]|9|@]|A|$]]]|J|$]]

<pre><code>select cast (as time(0))
</code></pre>

would be a good clause. For example:

<pre><code>(select cast(start_date as time(0))) AS 'START TIME'
</code></pre>

blocks|key|760236|text|要从datetime中获取时间，我们可以使用|type|unstyled|depth|inlineStyleRanges|entityRanges|data|760237|SELECT+CONVERT(VARCHAR(20),+GETDATE(),+114)|code-block|syntax|javascript|760238|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

To get the time from datetime, we can use

<pre><code>SELECT CONVERT(VARCHAR(20), GETDATE(), 114)
</code></pre>

blocks|key|760443|text|在MSSQL2012或更高版本|type|unstyled|depth|inlineStyleRanges|entityRanges|data|760444|cast(dateadd(ms,datediff(ms,+[StartDateTime],+[StopDateTime]),0)+as+Time(0))|code-block|syntax|javascript|760445|...or...|760446|convert(time(0),dateadd(ms,datediff(ms,+[StartDateTime],+[StopDateTime]),0)+)|760447|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

on MSSQL2012 or above

<pre><code>cast(dateadd(ms,datediff(ms, [StartDateTime], [StopDateTime]),0) as Time(0))
</code></pre>

...or...

<pre><code>convert(time(0),dateadd(ms,datediff(ms, [StartDateTime], [StopDateTime]),0) )
</code></pre>

blocks|key|760403|text|select+convert(char(5),+tbl_CustomerBooking.CheckInTime,+108)+AS+[time]
from+tbl_CustomerBooking|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|760404|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>select convert(char(5), tbl_CustomerBooking.CheckInTime, 108) AS [time]
from tbl_CustomerBooking
</code></pre>

blocks|key|543961|text|SQL+Server+2012：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|543962|Select+TRY_CONVERT(TIME,+myDateTimeColumn)+from+myTable;|code-block|syntax|javascript|543963|就我个人而言，我更喜欢TRY_CONVERT()而不是CONVERT()。主要区别是:如果强制转换失败，TRY_CONVERT()将返回NULL，而CONVERT()将引发错误。|543964|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

SQL Server 2012:

<pre><code>Select TRY_CONVERT(TIME, myDateTimeColumn) from myTable;
</code></pre>

Personally, I prefer TRY_CONVERT() to CONVERT(). The main difference: If cast fails, TRY_CONVERT() returns NULL while CONVERT() raises an error.

blocks|key|760499|text|您可以使用以下命令：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|760500|SELECT+CONVERT(VARCHAR(5),+GETDATE(),8)++|code-block|syntax|javascript|760501|输出：|760502|08:24|760503|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

You can use this:
<pre><code>SELECT CONVERT(VARCHAR(5), GETDATE(),8) 
</code></pre>
Output:
<pre><code>08:24
</code></pre>

blocks|key|760333|text|select+substr(to_char(colUmn_name,+'DD/MM/RRRR+HH:MM:SS'),11,19)+from+table_name;|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|760334|输出:来自|760335|05:11:26
05:11:24
05:11:24|code-block|syntax|javascript|760336|entityMap^0|0|29|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|R|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|S|8|@]|D|@]|E|$K|L]]|$1|M|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|N|$]]

<code>select substr(to_char(colUmn_name, 'DD/MM/RRRR HH:MM:SS'),11,19) from table_name;</code>

Output: from 

<pre><code>05:11:26
05:11:24
05:11:24
</code></pre>

I want to get only Time from DateTime column using SQL query 
using SQL Server 2005 and 2008
Default output:

<pre><code>AttDate 
==
2011-02-09 13:09:00 
2011-02-09 14:10:00 
</code></pre>

I'd like this output:

<pre><code>AttDate Time 
==
2011-02-09 13:09:00 13:09
2011-02-09 14:10:00 14:10
</code></pre>

How to get Time from DateTime format in SQL?

SQL Server

我想使用SQL Server2005和2008的SQL query从DateTime列中仅获取时间默认输出：AttDate                   ==2011-02-09 13:09:00    2011-02-09 14:10:00    我想要这个输出：AttDate                Time ==2011-02-09 13:09:00    13:092011-0

问如何在SQL中从DateTime格式中获取时间？
EN

回答 14

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在SQL中从DateTime格式中获取时间？EN

回答 14

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在SQL中从DateTime格式中获取时间？
EN