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.
