从日期获取时间?

How can I select the hour from a datetime?

如何从datetime中选择小时?

I'm looking for the equivalent of Oracle's TO_CHAR( d, 'HH24' ), which would return 17 for 2010-06-23 17:22:31.

我正在寻找等同于Oracle的TO_CHAR(d， 'HH24')，它将在2010-06-23 17:22:31返回17。

I tried to find out about the best time (best answer/question-ration) to ask an SQL-related question on *, using Data Explorer. I got it to work, but I'm sure there is a more elegant version.

我试图找出在*上使用Data Explorer询问sql相关问题的最佳时间(最佳答案/问题定量)。我让它工作，但我确信有一个更优雅的版本。

Select
  Left( Convert(VARCHAR(10), creationDate, 8 ), 2 ) As hour,
  Convert(Float, Count(*)) / Count(Distinct p.parentId) As ratio
From posts p
Join postTags pt On ( pt.postId = p.parentId )
Join tags t On ( t.id = pt.tagId )
Where p.postTypeId = 2 And t.tagName = '##TagName##'
Group By Left( Convert(VARCHAR(10), creationDate, 8 ), 2 )
Order BY hour

BTW, the best time seems to be between 15:00 and 16:00 - guess I'm asking too late :)

顺便说一句，最好的时间是在15:00到16:00之间——我想我问得太晚了:

6 个解决方案

#1

Use DATEPART

使用DATEPART

select DATEPART(hour, DateTimeField) as Hour from Table

#2

This should work

这应该工作

SELECT DatePart(hh, GETDATE())

#3

Use the DatePart() tsql function with the part ID of "hour"

使用DatePart() tsql函数，其ID为“hour”

Look at this for a good explanation

看看这个，好解释

http://support.microsoft.com/kb/186265

#4

SELECT HOUR(datetimefield) AS hour

The HOUR() function should do the job. Source http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_hour.

HOUR()函数应该完成这项工作。http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html function_hour来源。

#5

Select DatePart(hh, CreationDate) As Hour
    , Convert(Float, Count(*)) / Count(Distinct p.parentId) As Ratio
From posts As p
    Join postTags As pt 
        On ( pt.postId = p.parentId )
    Join tags As t 
        On ( t.id = pt.tagId )
Where p.postTypeId = 2 
    And t.tagName = '##TagName##'
Group By DatePart(hh, CreationDate)

#6

datepart(hh,dateColumn) should get you the hour component for any datetime.

datepart(hh,dateColumn)应该为任何datetime提供一个小时组件。

#1