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.
date parameter is a date or time value and can be a
time data type.
The return value is an integer that represents the hour part of the specified time, ranging from 0 to 23.
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.
The following are two examples to illustrate the usage of the
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
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:
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
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:
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.