Extracting hours from a DateTime (SQL Server 2005)

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?

12 Answers

SELECT DATEPART(HOUR, GETDATE()); 

DATEPART documentation

... 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")

Use datepart.

E.g.:

datepart(hh, date) 

try this one too:

 DATEPART(HOUR,GETDATE()) 

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 

DATEPART(HOUR, [date]) returns the hour in military time ( 00 to 23 ) If you want 1AM, 3PM etc, you need to case it out:

SELECT Run_Time_Hour = CASE DATEPART(HOUR, R.date_schedule) WHEN 0 THEN '12AM' WHEN 1 THEN '1AM' WHEN 2 THEN '2AM' WHEN 3 THEN '3AM' WHEN 4 THEN '4AM' WHEN 5 THEN '5AM' WHEN 6 THEN '6AM' WHEN 7 THEN '7AM' WHEN 8 THEN '8AM' WHEN 9 THEN '9AM' WHEN 10 THEN '10AM' WHEN 11 THEN '11AM' WHEN 12 THEN '12PM' ELSE CONVERT(varchar, DATEPART(HOUR, R.date_schedule)-12) + 'PM' END FROM dbo.ARCHIVE_RUN_SCHEDULE R 

Try this one too:

SELECT CONVERT(CHAR(8),GETDATE(),108) 

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

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

To include AM / PM - use the below:

SELECT concat(case when datepart(hour,getdate()) % 12 = 0 then 12 else datepart(hour,getdate()) % 12 end, case when datepart(hour,getdate()) < 12 then ' AM' else ' PM' end ) 

select convert(time,GETDATE()) 

you must use datepart()

like datepart(hour , getdate())