Convert day, hour, minute, second (6d 5h 19m 18s) in Hour’s integer value in SQL Server

manish verma 461

Hi All, we have a issue to convert Day, Hours, Minute, Second in Hours integer value. This is incoming data format-6d 5h 19m 18s, we need to convert this value in hours as integer value. Thanks MV

GeethaThatipatri-MSFT 29,477 Microsoft Employee

@manish verma Thanks for posting your question in the Microsoft Q&A forum.

Can you try as below, if this helps?

DECLARE @input VARCHAR(20) = '6d 5h 19m 18s'

SELECT DATEDIFF(HOUR, 0, DATEADD(SECOND, 
    CAST(SUBSTRING(@input, CHARINDEX('s', @input) - 2, 2) AS INT),
    DATEADD(MINUTE, 
        CAST(SUBSTRING(@input, CHARINDEX('m', @input) - 2, 2) AS INT),
        DATEADD(HOUR, 
            CAST(SUBSTRING(@input, CHARINDEX('h', @input) - 2, 2) AS INT),
            DATEADD(DAY, 
                CAST(SUBSTRING(@input, 1, CHARINDEX('d', @input) - 1) AS INT), 0)
            )
        )
    )
) AS [Total Hours]

Regards

Geetha

Accepted answer

LiHongMSFT-4306 27,961 Reputation points

2024-01-24T01:40:43.31+00:00

Hi @manish verma

This is incoming data format-6d 5h 19m 18s, we need to convert this value in hours as integer value.

As integer value? You can only convert it to decimal in hours, unless you don't need the precision of the data and strip out the minutes and seconds.

This is incoming data format-6d 5h 19m 18s

Is this data stored as varchar datatype or others? Please clarify this and post your desired result of conversion.

Best regards, Cosmog Hong
Please sign in to rate this answer.
manish verma 461 Reputation points

2024-01-24T06:31:10.15+00:00

Thanks a lot
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Convert day, hour, minute, second (6d 5h 19m 18s) in Hour’s integer value in SQL Server

0 additional answers

Your answer