Converting minutes to hours is a common requirement when working with timerelated data in SQL. For example, you might need to calculate the number of hours an employee worked based on their total minutes worked. In this article, we will explore different methods to convert minutes to hours in SQL with code examples.
Method 1: Using SQL Functions
SQL provides a few builtin functions to convert timerelated data. One of them is the DATEADD function, which allows us to add or subtract a specified time interval from a date or time value.
To convert minutes to hours using the DATEADD function, we can divide the total minutes by 60 and add the result to a default date of '19000101'. Here is the SQL code:
DECLARE @TotalMinutes INT = 125;
SELECT DATEADD(HOUR, @TotalMinutes/60, '19000101');
This code will return the result as a datetime value, which needs to be formatted to display only the hour and minute part. We can use the CONVERT function to format the datetime value as a string in the desired format. Here is the updated code:
DECLARE @TotalMinutes INT = 125;
SELECT CONVERT(varchar(5), DATEADD(HOUR, @TotalMinutes/60, '19000101'), 108) AS HoursAndMinutes;
This code will return the result in the format of 'hh:mm', where 'hh' represents the hours and 'mm' represents the minutes.
Method 2: Using Custom Functions
If you need to convert minutes to hours frequently in your SQL queries, you can create a custom function to simplify the process. In this method, we will create a userdefined function (UDF) that takes a total number of minutes as input and returns the equivalent hours and minutes as a string.
Here is the SQL code to create the UDF:
CREATE FUNCTION ConvertMinutesToHours (@TotalMinutes INT)
RETURNS VARCHAR(5)
AS
BEGIN
DECLARE @HoursAndMinutes VARCHAR(5);
SET @HoursAndMinutes = CONVERT(varchar(5), DATEADD(HOUR, @TotalMinutes/60, '19000101'), 108);
RETURN @HoursAndMinutes;
END;
After creating the UDF, we can use it in our SQL queries to convert minutes to hours. Here is an example:
SELECT ConvertMinutesToHours(125) AS HoursAndMinutes;
This code will return the result in the same format as the previous method.
Method 3: Using Mathematical Operations
Another way to convert minutes to hours in SQL is to use mathematical operations. We can divide the total minutes by 60 to get the integer part of the hours and use the modulo operator (%) to get the remaining minutes. Here is the SQL code:
DECLARE @TotalMinutes INT = 125;
SELECT CAST(@TotalMinutes / 60 AS VARCHAR(2)) + ':' + CAST(@TotalMinutes % 60 AS VARCHAR(2)) AS HoursAndMinutes;
This code will return the result as a string in the format of 'HH:MM', where 'HH' represents the hours and 'MM' represents the minutes.
Conclusion
Converting minutes to hours is a common requirement in SQL when working with timerelated data. In this article, we explored three different methods to convert minutes to hours in SQL: using SQL functions, custom functions, and mathematical operations. Each method has its own advantages and disadvantages, and you should choose the one that best suits your needs.
Sure! Here are some additional details on each of the topics I covered in the previous article:
Method 1: Using SQL Functions
The DATEADD function in SQL is very useful when working with timerelated data. It allows you to add or subtract a specified time interval (such as years, months, days, hours, minutes, seconds, etc.) from a date or time value. In our example, we used the HOUR interval to add the number of hours calculated from the total minutes to a default date of '19000101'. This gives us a datetime value that includes the correct number of hours and minutes.
The CONVERT function in SQL allows you to convert a data type to another data type. In our example, we used it to convert the datetime value returned by the DATEADD function to a string in the format of 'hh:mm'. The '108' parameter in the CONVERT function specifies the style in which the datetime value should be formatted. There are many different styles available in SQL, so you can choose the one that best suits your needs.
Method 2: Using Custom Functions
Creating a custom function in SQL allows you to encapsulate complex logic into a single reusable unit. In our example, we created a UDF called ConvertMinutesToHours that takes a total number of minutes as input and returns the equivalent hours and minutes as a string. The function uses the same logic as Method 1 to calculate the number of hours and minutes, but it encapsulates that logic into a single function that can be called from any SQL query. This makes it easy to convert minutes to hours in multiple queries without having to repeat the same code over and over.
Method 3: Using Mathematical Operations
Using mathematical operations to convert minutes to hours in SQL is a simple and efficient way to do the conversion. In our example, we used integer division (by dividing the total minutes by 60) to get the integer part of the hours, and the modulo operator (by using the % symbol) to get the remaining minutes. We then concatenated these values together into a single string in the format of 'HH:MM'. This method is useful when you need to quickly convert minutes to hours in a simple SQL query without the need for complex functions or logic.
Overall, each of these methods for converting minutes to hours in SQL has its own advantages and disadvantages. Some are more complex but offer more flexibility, while others are simpler but offer less customizability. You should choose the method that best suits your needs based on the specific requirements of your SQL query.
Popular questions
Sure, here are five questions related to the topic:

What is the DATEADD function used for in SQL?
Answer: The DATEADD function in SQL is used to add or subtract a specified time interval (such as years, months, days, hours, minutes, seconds, etc.) from a date or time value. 
How can you format a datetime value as a string in SQL?
Answer: You can use the CONVERT function in SQL to format a datetime value as a string in a specific format. The CONVERT function takes three arguments: the data type you want to convert to, the expression to be converted, and the style in which you want the expression to be formatted. 
What is a userdefined function (UDF) in SQL used for?
Answer: A userdefined function (UDF) in SQL is used to encapsulate complex logic into a single reusable unit, making it easier to write SQL queries that require that logic. UDFs can take one or more parameters as input and return a value as output. 
What is the modulo operator used for in SQL?
Answer: The modulo operator, denoted by the % symbol, is used in SQL to calculate the remainder of a division operation. In the context of converting minutes to hours, it can be used to calculate the remaining minutes that don't make up a full hour. 
Which method for converting minutes to hours in SQL is the most efficient?
Answer: Using mathematical operations to convert minutes to hours in SQL is generally the most efficient method, as it requires only basic arithmetic operations and produces the result in a simple format. However, this method may not be as flexible or customizable as using SQL functions or UDFs.
Tag
TimeConversion