October 24, 2004 at 3:01 pm
I have a field that contains nvarchar datatype in the format that can vary from h:mm to hhhh:mm, for example, 211:34. That's 211 hours and 34 minutes. I need to convert that to 12694 minutes or 211.56 hours. I'm having a tough time splitting the hrs, from the min. Any suggestions would be much appreicated.
Thanks,
JR
October 24, 2004 at 7:10 pm
Try this,
DECLARE @Time NVARCHAR(20)
DECLARE @Minutes INT
DECLARE @NewTime NVARCHAR(20)
DECLARE @HoursToMinutes INT
DECLARE @Hours INT
DECLARE @Position INT
SET @Time = '211:34'
SET @Position = PATINDEX('%:%', @Time)
SET @Hours = SUBSTRING(@Time, 0, @Position)
SET @Minutes = RIGHT(@Time, 2)
SET @HoursToMinutes = @Hours * 60
SELECT @NewTime = @HoursToMinutes + @Minutes
SELECT @NewTime
October 24, 2004 at 9:10 pm
Here's another (similar) way:
declare @time nvarchar(20)
declare @timefloat float
declare @minutes int
set @time = '211:34'
set @timefloat = replace(@time,':','.')
set @minutes = floor(@timefloat) * 60 + (@timefloat - floor(@timefloat)) * 100
Regards
Phil
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 24, 2004 at 9:58 pm
Thank you both for the replies. Honestly, I am really a novice with SQL. I'm using SQL Query Analyzer and I plan to dump the results into excel and create a pivot table. I know that I can do the calculations in excel, but I want to do them the analyzer if possible. I have tried using pretty much all of the string functions that I could read up on and I still can't get it to work.
October 25, 2004 at 3:01 am
Hi,
Try this in QA, and see if you get any wiser..
create table #t(hhhh_mm nvarchar(20))
insert #t(hhhh_mm) select N'211:34' union select N'11:34' union select N'1:34'
select hhhh_mm, left(hhhh_mm,charindex(N':',hhhh_mm)-1) * 60 + right(hhhh_mm,2) as minutes
from #t
drop table #t
You must unlearn what You have learnt
October 25, 2004 at 1:21 pm
Hello,
All three suggestions are correct. If you always have the minutes portion as a two-digit number, like these examples:
zTime
118:45
120:55
122:15
Then this query SQL should work in QA:
SELECT substring(zTime,1,len(zTime)-3) as hrs,
substring(zTime,len(zTime)-1,2) as min,
convert(int,substring(zTime,1,len(zTime)-3))*60
+ convert(int,substring(zTime,len(zTime)-1,2)) as total_min
FROM
sample_table
With this result set:
hrs min total_min
118 45 7125
120 55 7255
122 15 7335
Bob Monahon
October 26, 2004 at 8:25 pm
Thanks, I will give it a try.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply