SQL HOUR() Function


The SQL HOUR() is a function, and return or extract the hour portion of a given input time value.

The SQL HOUR() function returns a integer number specifying a whole number between 0 and 23, inclusive, representing the hour of the day.

The SQL HOUR() function is supports only datetime or timestamp based table columns or fields.

It can be used in SELECT statement as well in where clause.


SQL HOUR() Syntax

The below syntax is used to get hour of the day value (a integer number between 0 to 23, inclusive) from a given input datetime value.

For SQL SERVER / MY SQL / MS ACCESS


SELECT HOUR('valid_datetime_value' or 'valid_timestamp_value');


SQL HOUR() Example - Using Expression Or Formula

The following SQL SELECT statement will extract the hour of the day value from a given input datetime value. We use now() function to get the input date value.


SELECT 
NOW() AS 'Current Date and Time', 
HOUR(NOW()) AS 'Hour Value';

The result of above query is:

Current Date and Time Hour Value
24-08-2012 19:37:30 19

Sample Database Table - BookOrder

BookID BookName OrderDate Qty DeliveryDate
1 Art Of SQL 12-01-2010 18:24:46 2 20-01-2010 07:47:23
2 Programming Microsoft sql Server 2012 12-03-1998 21:51:34 1 15-03-1998 07:52:29
3 SQL Puzzles & Answers 24-07-2000 07:04:50 19 26-07-2000 12:42:40
4 Oracle Plsql Programming Fundamentals 19-09-2005 18:58:39 6 22-10-2005 06:17:45

SQL HOUR() Example - With Table Column

The following SQL SELECT statement display the column "OrderDate" and "DeliveryDate" from the "BookOrder" table. The HOUR() function will extract the HOUR value(a whole integer number from 1 to 24) from the column "OrderDate" and we will stored that value to a new column called "Order HOUR" and "Delivery HOUR".


SELECT 
OrderDate, HOUR(OrderDate) As 'Order HOUR', 
DeliveryDate, HOUR(DeliveryDate) As 'Delivery HOUR' 
FROM BookOrder;

The result of above query is:

OrderDate Order HOUR DeliveryDate Delivery HOUR
12-01-2010 18:24:46 18 20-01-2010 07:47:23 7
12-03-1998 21:51:34 21 15-03-1998 07:52:29 7
24-07-2000 07:04:50 7 26-07-2000 12:42:40 12
19-09-2005 18:58:39 18 22-10-2005 06:17:45 6