Getting hours from a date?

Question

How to choose an hour from datetime?

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

---

I tried to find out about the best time (best answer / question-ration) to ask a SQL-related question about StackOverflow using the 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

---

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

Answer 1

Use DATEPART

select DATEPART(hour, DateTimeField) as Hour from Table

Answer 2

DatePart() tsql ""

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

Answer 3

This should work

SELECT DatePart(hh, GETDATE()) 

Answer 4

datepart(hh,dateColumn) should provide you with an hour component for any time and time.

Answer 5

SELECT HOUR(datetimefield) AS hour

The HOUR () function must 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)