SQL Server Integer to Time

Question

I came into a scenario where table containing the time values in integer datatype and I need to convert them to TIME datatype to use DATEDIFF() function.

Ex: if column value is 449, then it's 04:49:00:00; if the column contains 25, then 02:05:00:00.

If anyone has a user-defined procedure or code that should be more helpful to use them in Select statement as I need to pass this value to DATEDIFF().

25 -> 02:05 looks inconsistent with 449-> 04:49. Can you provide more examples? — Alex Yu, Sep 28 '19 at 18:58
This dies not solve the 25 >> 2:05. I suspect a typo. Otherwise try Select timefromparts(449/100 ,449%100 ,0 ,0,0 ) — John Cappelletti, Sep 28 '19 at 20:28
Ignoring that 25 somehow becomes 02:05:00.000, this sounds like the silly encodings used in `run_date` and `run_time` in the Jobs tables in msdb. In that case there's an undocumented function that turns those values back into a `datetime` value, `msdb.dbo.agent_datetime(run_date, run_time)`. — AlwaysLearning, Sep 29 '19 at 01:06

P Kernel · Accepted Answer · 2019-09-29T17:10:06.773

TRY THIS!!!!!

    CREATE FUNCTION dbo.udfConv(
    @quantity INT
)
RETURNS VARCHAR(25)
AS 
BEGIN
    RETURN (SELECT CAST(CASE WHEN LEN(@quantity)=2 THEN '0'+LEFT(@quantity,1) +':'+'0'+RIGHT(@quantity,1)+':00:00'
            WHEN LEN(@quantity)=3 THEN '0'+LEFT(@quantity,1) +':'+RIGHT(@quantity,2)+':00:00'
            WHEN LEN(@quantity)=4 THEN LEFT(@quantity,2) +':'+RIGHT(@quantity,2)+':00:00'
 END AS TIME))
END;

THIS IS NOT EXACT THE DATETIME FORMAT. DATETIME FORMAT IS SOMETHING LIKE DD-MM-YYYY HH:MM: SS. HOWEVER, THIS QUESTION NEEDS MORE IMPROVEMENT.

SQL Server Integer to Time

1 Answers1