Converts the 5 character string duration (ie hh:nn) to minutes

Dear All,

I wanted to convert 5 characters string duration (ie hh:mm - 10:30) to minutes and convert back the resulting minutes to 5 character string duration using a scalar UDFs in Sqlserver 2000. How do i write the script to obtain the desired output.

I badly need some from someone.

Give few set of example. do you want result 10 *60 + 30 for 10:30..|||

For example, if duration is 10 hrs. 30 mins. written in 10:30 format should be convert to minutes, means convert 10 hours into minutes and add 30 mins to it will be the resulting output = 630 minutes this should be convert back into the previous (10:30) format.

Thanks

|||

here you go...

Code Snippet

Create Table #times (

[Time] Varchar(100)

);

Insert Into #times Values('10:30');

Insert Into #times Values('12:34');

Insert Into #times Values('15:45');

Select

Datediff(Mi,Cast('00:00' as datetime) ,Cast([Time] as Datetime))

From

#times

--or

Select

Substring([Time],1,Charindex(':',[Time])-1) * 60

+ Substring([Time],Charindex(':',[Time]) + 1,10)

From

#Times

|||

Thanks alot for the help. And how about the reverse of that, ie, Converts the resulting minutes to 5 character string duration

|||

Yes.. here it is...

Code Snippet

Create Table #mindata (

[Mint] int

);

Insert Into #mindata Values('630');

Insert Into #mindata Values('754');

Insert Into #mindata Values('945');

Select

Cast(Mint/60 as Varchar) + ':' + Cast(Mint%60 as Varchar)as [Time]

From

#mindata

|||

Dear Manivannan.D.Sekaran,

Thank you very very very much.

Converting percent-decoded data, or characters to int

Say I have a percent encoded expression like www%2emysite%2ecom. An unencoded representation would be www.mysite.com.

I have written a tsql regex function to parse an input string and replace any legal percent-encoded substring with its ascii equivalent, returning the unencoded version of the input string. However I'm stuck on one crucial step: converting a percent-encoded substring (ex: '%2e') or its hexadecimal string representation ('0x2e') to an integer.

I've tried CONVERT(bigInt, '0x2e') but of course that isn't working.

What is the best way to go about this? I've been all over the sql server books online, search engines, and other forums. I can't get past this one step. Am I on the right track, or should I look at it a different way?

Without knowing more about the rest of your code, you can do this: exec ('select CONVERT(int, ' + '0x2e' + ') '), though not in a t-sql function (but I don't know how you are doing regex in a T-SQL function)

|||

you have to convert the varchar value to binary value..

You can't direcly assign this value from the varchar varibale/column, if you convert the varchar value it will create a binary equalent of the string..

I once used the following logic to overcome this issue,

Declare @.ValueString as Varchar(100)
Set @.ValueString = '0x2e'

Declare @.Value as VarBinary
Declare @.sql as NVarchar(1000)

Select @.sql = N'select @.val=' + @.ValueString
Exec sp_executesql @.sql, N'@.val varbinary output', @.Value output

Select char(convert(bigint, @.value))

--Result: .

Here we are assinging the Binary value directly to the VarBinary variable, with out using the convert function..But i am not sure how it will help you to create a generic function(since inside the funcation you can't able to call sp_executesql)..

|||Thank you.

I don't know how you are doing regex in a T-SQL function

My t-sql regex find function works via vbScript RegExp class. I'm sure it's not the most efficient ,but it does work very well.

I still haven't made any progress with the current issue. I don't understand why a character expression that is also a well-formed hexadecimal number can't be easily converted into an integer. Sigh...