Getting the hour from a datetime?

Question

How can I select the hour from a 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.

---

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

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 :)

Answer 1

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

Look at this for a good explanation

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

Answer 2

Use DATEPART

select DATEPART(hour, DateTimeField) as Hour from Table

Answer 3

This should work

SELECT DatePart(hh, GETDATE()) 

Answer 4

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

Answer 5

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.

Answer 6

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)