string values,...convert into hours

raj thadak 21 Reputation points
2021-08-27T09:50:31.94+00:00

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,584 questions
{count} votes

4 answers

Sort by: Most helpful
  1. Viorel 114K Reputation points
    2021-08-27T11:18:57.75+00:00

    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.

    1 person found this answer helpful.
    0 comments No comments

  2. raj thadak 21 Reputation points
    2021-08-27T11:46:45.89+00:00

    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

    0 comments No comments

  3. Vladimir Moldovanenko 251 Reputation points
    2021-08-29T12:31:02.477+00:00

    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  
          
      
    
    0 comments No comments

  4. MelissaMa-MSFT 24,176 Reputation points
    2021-08-30T05:55:50.79+00:00

    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.

    0 comments No comments