SQL Server HOUR() Function

In SQL Server, the HOUR() function is used to retrieve the hour part of a given time value. This function is one of the built-in date/time functions and can be used conveniently for handling time data.

Syntax

HOUR(date)

The date parameter is a date or time value and can be a datetime, date, datetime2, or time data type.

The return value is an integer that represents the hour part of the specified time, ranging from 0 to 23.

Use Cases

The HOUR() function is commonly used in the following scenarios:

• When it is necessary to extract the hour part from a date/time value for calculation or comparison.
• When it is necessary to convert a date/time value to a specific time format, such as HH:mm:ss.
• When it is necessary to group or aggregate data by hour for summary or statistical analysis.

Examples

The following are two examples to illustrate the usage of the HOUR() function.

Example 1

Suppose there is an order table orders that includes a column create_time for the time when the order was created. Now, it is required to count the number of orders created in each hour.

SELECT HOUR(create_time) AS hour_of_day, COUNT(*) AS order_count
FROM orders
GROUP BY HOUR(create_time)

After executing the above SQL query, a result set will be obtained, where each row represents the number of orders created in one hour. For example, here is part of the result:

Example 2

Suppose there is a time value @time_value with a value of '2023-03-11 15:30:45', and it is required to convert it to a string with the format HH:mm:ss.

SELECT CONVERT(VARCHAR(8), @time_value, 108) AS formatted_time

After executing the above SQL query, a result set with only one row representing the formatted time value will be obtained. For example, here is the result:

Conclusion

The HOUR() function is a convenient date/time function that can be used to extract the hour part from a date/time value and group or aggregate data by hour. It is frequently used in practical data processing and analysis tasks, so mastering its usage is essential.