question

rkvvrkvarma avatar image
rkvvrkvarma asked

Can anyone help me how to write a scalar valued function(user defined) to convert hours,minutes into seconds.

I am getting the input as 3 hour 5 minutes, 1536 hours 36 minutes, 56 minutes from atable. I need to convert them into seconds and store them in another table. Can anyone help me on solving this issue?
mysqlstored procedure
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

What have you tried thus far?
0 Likes 0 ·
Oleg avatar image
Oleg answered
Here is the version of the function which will accommodate more than 3 hours/minutes digits. It will work provided that the input hours and minutes represent the time span less than about 60 years (you can only fit up to 2 billion into integer data type and there are about 31 million seconds per year). The function is based on the following logic: The input is assumed to include hours or minutes or both hours and minutes. Both words may be spelled as singular or plural (hour or hours, minute or minutes). Therefore, let's: 1. Replace the hour (or hours) with the pipe, minute (or minutes) with blank, replace all spaces with blank, and then check the resulting value (whatever is left from input after these manipulations) 2. If the value is in the form d|d where d is any number of digits then both hours and minutes were present 3. If the value is in the form d| then the input was without minutes (for example, 75 hours) 4. if the value is in the form d then the input was without hours, just minutes (for example, 56 minutes) 5. If the hours part is present then multiply it by 3600 and add the minutes (if present) multiplied by 60. I noticed that the function in question was created with **ansi\_nulls off**. This should ***never*** happen for this spefication is evil. However, because this is one of the "sticky" settings, it is not possible to change it after the function has been already created. Thus, I would strongly recommend dropping the function and then creating it with proper settings instead of simply altering existing function because altering will have no effect on any attempt to modify the sticky settings. Here is the function definition: set ansi_nulls on; set quoted_identifier on; go create function dbo.udf_GetSecondsFromStringInput (@string varchar(100)) returns int as begin select @string = replace(replace(replace(replace(replace( @string, 'hours', '|'), 'hour', '|'), 'minutes', ''), 'minute', ''), ' ', '') declare @ret int; declare @pos int = charindex('|', @string) if @pos = len(@string) -- input value did not include minutes, just hours set @ret = substring(@string, 1, len(@string) - 1) * 3600; else if @pos = 0 -- input value did not include hours, just minutes set @ret = @string * 60; else -- input included both hour(s) and minutes set @ret = substring(@string, 1, @pos - 1) * 3600 + substring(@string, @pos + 1, 10) * 60 return @ret; end; go I ran some tests using the following sample: declare @t table(input varchar(100) not null); insert into @t values ('3 hour 5 minutes'), ('1536 hours 36 minutes'), ('56 minutes'), ('0 hours 1 minute'), ('234894 hours 54 minutes'), ('75 hour'), ('1000 hours 1 minute'), ('2 minute'); select *, dbo.udf_GetSecondsFromStringInput(input) seconds from @t; The test script above produced the following results: input seconds ----------------------- ----------- 3 hour 5 minutes 11100 1536 hours 36 minutes 5531760 56 minutes 3360 0 hours 1 minute 60 234894 hours 54 minutes 845621640 75 hour 270000 1000 hours 1 minute 3600060 2 minute 120 Hope this helps. Oleg
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

rkvvrkvarma avatar image
rkvvrkvarma answered
@JohnM I created the below mentioned function. Its working fine when I am getting input in 3 digits in hour format. But when I get the input in more than 3 digits in hour format. (For example 1534 hours 34 minutes ,234894 hours 54 minutes), my function is not able to convert it into seconds.. Here is my function /****** Object: UserDefinedFunction [dbo].[BUSINESS_DURATION_MINTS_FN] Script Date: 10/18/2017 9:24:36 AM ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[BUSINESS_DURATION_MINTS_FN] (@string varchar(100) ) RETURNS INT AS BEGIN DECLARE @total_mints INT DECLARE @hours INT DECLARE @Mints INT DECLARE @Seconds INT IF @string IS NOT NULL BEGIN SET @string = REPLACE(UPPER(@string),'HOURS','HOUR') SET @string = REPLACE(UPPER(@string),'MINUTES','MINUTE') IF ((CHARINDEX(UPPER('HOUR'),UPPER(@string))!=0) AND (CHARINDEX(UPPER('MINUTE'),UPPER(@string))!= 0)) BEGIN SET @hours = CONVERT(INT,SUBSTRING(@string,1, (CHARINDEX(UPPER('HOUR'), UPPER(@string))-2))) SET @Mints = CONVERT(INT,SUBSTRING(@string,(CHARINDEX(UPPER('HOUR'),UPPER(@string))+4),(CHARINDEX(UPPER('MINUTE'),UPPER(@string))-(CHARINDEX(UPPER('HOUR'), UPPER(@string))+5)))) END IF ((CHARINDEX(UPPER('HOUR'),UPPER(@string))=0) AND (CHARINDEX(UPPER('MINUTE'),UPPER(@string))!= 0)) BEGIN SET @hours = 0 SET @Mints = CONVERT(INT,SUBSTRING(@string,1, (CHARINDEX(UPPER('MINUTE'), UPPER(@string))-2))) END IF ((CHARINDEX(UPPER('HOUR'),UPPER(@string))!=0) AND (CHARINDEX(UPPER('MINUTE'),UPPER(@string))=0)) BEGIN SET @hours = CONVERT(INT,SUBSTRING(@string,1, (CHARINDEX(UPPER('HOUR'), UPPER(@string))-2))) SET @Mints = 0 END IF ((CHARINDEX(UPPER('HOUR'),UPPER(@string))= 0) AND (CHARINDEX(UPPER('MINUTE'),UPPER(@string))=0)) BEGIN SET @hours = 0 SET @Mints = 0 END SET @total_mints = @hours*60 + @Mints SET @Seconds = @total_mints*60 END IF @string IS NULL BEGIN SET @Seconds = NULL END RETURN @Seconds END GO
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.