string values,...convert into hours

Question

Hi All,

Thanks a lot for your time; i have seen many examples where we convert the datetime into days:hours:mins - but i have the other way around - i have a string or text column which has values like
Ticket_Number Total Duration

JIRA123                             2 Days 10 Hours 15 Minutes

JIRA456                             0 days 1 hour 12 minutes

JIRA999                             45 minutes

JIRA888                           13 minutes

JIRA777                           null

JIRA666                         15 days 17 hours 45 minutes

What do I Need? convert into hours - show in hours like 18.5 or 19.56 hours

i have the SQL server table with this layout.. without going for stored procedures/functions is this something that can be done using the select clause..

again, thanks a lot

Answer 1

In case of the shown variants, try these manipulations:

select Ticket_Number,
    case when [Total Duration] is null then null
    else
        format((substring(c, 1, pd-1) * 24 * 60 + substring(c, pd+3, ph-pd-3) * 60 + substring(c, ph+4, len(c)-ph-4+1))/60.0, '0.##')
    end as [Total Duration (hours)]
from MyTable
cross apply (values (replace(replace(replace(replace([Total Duration], 'days', 'day'), 'hours', 'hour'), 'minutes', ''), 'minute', ''))) t1(a)
cross apply (values (concat( case when a not like '%hour%' then '0 hour ' end, a))) t2(b)
cross apply (values (concat( case when b not like '%day%' then '0 day ' end, b))) t3(c)
cross apply (values (charindex('day', c), charindex('hour', c))) t4(pd, ph)

If there are more variants, then adjust the query or show details.

Answer 2

Hi Viorel, thanks a lot, the only issue i see with this is my table has millions of record (13 millions plus)...
let me test.. any other option

Answer 3

Here is another version I posted for you here

https://techcommunity.microsoft.com/t5/sql-server/string-column-values-convert-into-hours/m-p/2688949

DECLARE @tmp TABLE  
(  
     Ticket_Number nvarchar(100)  
     ,TotalDuration varchar(100)  
)  
INSERT INTO @tmp VALUES  
    ('JIRA123','2 Days 10 Hours 15 Minutes')  
    ,('JIRA456','0 days 1 hour 12 minutes')  
    ,('JIRA999','45 minutes')  
    ,('JIRA888','13 minutes')  
    ,('JIRA777','null')  
    ,('JIRA666','15 days 17 hours 45 minutes')  
    ,('JIR???','17 hours 45 minutes') -- added, to prove pattern working   
  
SELECT  
    t.*  
    ,clean.TotalDuration  
    ,cleaned.TotalDuration  
    -- this is what you want  
    ,Time_Hours = NULLIF(CAST(  
        ISNULL(TRY_CAST(PARSENAME(cleaned.TotalDuration, 3) as decimal(9,2)) * 24, 0) -- days  
        + ISNULL(TRY_CAST(PARSENAME(cleaned.TotalDuration, 2) as decimal(9,2)), 0) -- hours  
        + ISNULL(TRY_CAST(PARSENAME(cleaned.TotalDuration, 1) as decimal(9,2)) / 60.0, 0) -- minutes  
        as decimal(9,2)), 0)  
FROM @tmp t  
CROSS APPLY(SELECT TotalDuration = CASE WHEN t.TotalDuration != 'null' THEN REPLACE(REPLACE(t.TotalDuration, ' hour ', ' hours '), 'null', '') END) clean  
CROSS APPLY(SELECT TotalDuration = '"' + REPLACE(REPLACE(REPLACE(clean.TotalDuration, 'hours', '"."'), 'days', '"."'), 'minutes', '"')) cleaned  

or simpler

SELECT  
    t.*  
    ,clean.TotalDuration  
    ,cleaned.TotalDuration  
    -- this is what you want  
    ,Time_Hours = NULLIF(CAST(  
        ISNULL(TRY_CAST(PARSENAME(cleaned.TotalDuration, 3) as decimal(9,2)) * 24, 0) -- days  
        + ISNULL(TRY_CAST(PARSENAME(cleaned.TotalDuration, 2) as decimal(9,2)), 0) -- hours  
        + ISNULL(TRY_CAST(PARSENAME(cleaned.TotalDuration, 1) as decimal(9,2)) / 60.0, 0) -- minutes  
        as decimal(9,2)), 0)  
FROM @tmp t  
CROSS APPLY(SELECT TotalDuration = REPLACE(t.TotalDuration, ' hour ', ' hours ')) clean  
CROSS APPLY(SELECT TotalDuration = REPLACE(REPLACE(REPLACE(clean.TotalDuration, 'hours', '.'), 'days', '.'), 'minutes', '')) cleaned  

or another variation... you need to test performance of these to know what is better

    SELECT  
        t.*  
        ,clean.TotalDuration  
        ,cleaned.TotalDuration  
        -- this is what you want  
        ,Time_Hours = NULLIF(CAST(  
            ISNULL(TRY_CAST(PARSENAME(cleaned.TotalDuration, 3) as decimal(9,2)) * 24, 0) -- days  
            + ISNULL(TRY_CAST(PARSENAME(cleaned.TotalDuration, 2) as decimal(9,2)), 0) -- hours  
            + ISNULL(TRY_CAST(PARSENAME(cleaned.TotalDuration, 1) as decimal(9,2)) / 60.0, 0) -- minutes  
            as decimal(9,2)), 0)  
    FROM @tmp t  
    CROSS APPLY(SELECT TotalDuration = REPLACE(t.TotalDuration, ' hour ', ' hours ')) clean  
    CROSS APPLY(SELECT TotalDuration = TRANSLATE(clean.TotalDuration, 'dayshoursminutes', '.   .           ')) cleaned  
      
  

Answer 4

Hi @raj thadak ,

Welcome to Microsoft Q&A!

Please also refer below and check whether it is helpful:

create table TABLEAA  
 (  
      Ticket_Number nvarchar(100)  
      ,TotalDuration varchar(100)  
 )  
 INSERT INTO TABLEAA VALUES  
     ('JIRA123','2 Days 10 Hours 15 Minutes')  
     ,('JIRA456','0 days 1 hour 12 minutes')  
     ,('JIRA999','45 minutes')  
     ,('JIRA888','13 minutes')  
     ,('JIRA777','null')  
     ,('JIRA666','15 days 17 hours 45 minutes')  
  
select Ticket_Number, cast(days*24+hours+minutes/60.0 as decimal(5,2)) Hours  
from (  
select Ticket_Number,  
case when CHARINDEX('day',TotalDuration)>0 then SUBSTRING(TotalDuration,1,CHARINDEX('day',TotalDuration)-2) else 0 end days  
,case when CHARINDEX('hour',TotalDuration)>0 then SUBSTRING(TotalDuration,CHARINDEX('day',TotalDuration)+4,CHARINDEX('hour',TotalDuration)-CHARINDEX('day',TotalDuration)-4) else 0 end hours  
,case when CHARINDEX('minute',TotalDuration)>0 and CHARINDEX('hour',TotalDuration)>0 then SUBSTRING(TotalDuration,CHARINDEX('hour',TotalDuration)+5,CHARINDEX('minutes',TotalDuration)-CHARINDEX('hour',TotalDuration)-5)   
      when CHARINDEX('minute',TotalDuration)>0 and CHARINDEX('hour',TotalDuration)=0 then SUBSTRING(TotalDuration,1,CHARINDEX('minutes',TotalDuration)-1)   
      else 0 end minutes  
 from TABLEAA)a  

Output:

Ticket_Number	Hours  
JIRA123	58.25  
JIRA456	1.20  
JIRA999	0.75  
JIRA888	0.22  
JIRA777	0.00  
JIRA666	377.75  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.