How to Covert or split Total Minute into Hour and Minutes MySQL Stored procedure

Consider you want to pass total minutes as input parameter and you want  to get split it into hours and minutes so you can use this stored procedure .
Here we will pass Total minute as Input parameter and we will get Hour and minute in  HH:MM format as out parameter


/* Your Code Starts Here */
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `minutetohour`(In iMinute Int ,Out sHrMin varchar(50)) /* Declaring 1 Input Parameter and 1 Out Parameter */

BEGIN

Declare TotalHour,TotalMin int;
Declare HourandMin varchar(10);

set TotalHour=(Select FLOOR(`iMinute` / 60) AS Quotient);   /* Total Minute Devide by 60 and its quotient take as Hour */
set TotalMin=(Select `iMinute` mod 60 AS Remainder);  /*Took Mod of Total Minute with 60  and set it as minute */
set HourandMin =(SELECT CONCAT(CAST(`TotalHour` AS CHAR) ,':', CAST( `TotalMin` AS CHAR)));
 /* First Cast two integer values into string and concatenate it */
set sHrMin=HourandMin;  /* Set the values into Out put parameter*/

END

/* Your Code EndsHere */


Now run this query you will get the result
call minutetohour(700,@X);
select @X;

0 comments: