Extracting hours from a DateTime (SQL Server 2005)

Question

I can extract the month and day by using Day(Date()), Month(Date()). I can't extract hours, with HOUR(Date()). I get the following error.

'HOUR' is not a recognized built-in function name.

How can I extract hours?

Answer 1

... you can use it on any granularity type i.e.:

DATEPART(YEAR, [date])

DATEPART(MONTH, [date]) 

DATEPART(DAY, [date])    

DATEPART(HOUR, [date]) 

DATEPART(MINUTE, [date])

(note: I like the [ ] around the date reserved word though. Of course that's in case your column with timestamp is labeled "date")

Answer 2

The DATEPART() function is used to return a single part of a date/time, such as year, month, day, hour, minute, etc.

datepart    ***Abbreviation

year        ***yy, yyyy 
quarter     ***qq, q 
month       ***mm, m 
dayofyear   ***dy, y 
day         ***dd, d 
week        ***wk, ww 
weekday     ***dw, w 
hour        ***hh 
minute      ***mi, n 
second      ***ss, s 
millisecond ***ms 
microsecond ***mcs 
nanosecond  ***ns 

Example

select * 
from table001
where datepart(hh,datetime) like 23

Answer 3

I can't extract hours, with HOUR(Date())

There is a way to call HOUR (I would not recommend to use it though because there is DATEPART function) using ODBC Scalar Functions:

SELECT {fn HOUR(GETDATE())} AS hour

LiveDemo

Answer 4

SELECT DATEPART(HOUR, GETDATE());

DATEPART documentation

Answer 5

select case when [am or _pm] ='PM' and datepart(HOUR,time_received)<>12 
           then dateadd(hour,12,time_received) 
           else time_received 
       END 
from table

works

Answer 6

to_char(current_timestamp,'YYYY') // to extract only DATE to_char(current_timestamp,'HH24') // to extract only HOUR