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.