Convert Excel formula (using Date and subtraction) into T-SQL-CodePudding

I am trying to write this Excel formula into T-SQL (to write a function).

Expected output is 0.71944444, but currently my output (using T-SQL) is 24.0000.

I am not sure why we have to add a day to same date and subtract the same date.

Bottom is a screenshot from Excel:

This is what I have so far in T-SQL:

CREATE FUNCTION  [dbo].[fn_0921] (
    @Punch_Start nvarchar(max)
)
RETURNS  decimal(36, 8) AS

BEGIN   
    DECLARE @return_value nvarchar(max);
    
        SET @return_value =                         
                                    
                    DATEDIFF(
                            MINUTE, CAST(@Punch_Start AS datetime2), 
                                        (
                                            dateadd(
                                                    day, 1, CAST(@Punch_Start AS datetime2)
                                                    )
                                        )  
                            ) 
                    / (60.0)            
    RETURN @return_value
END;

Thanks for help.

CodePudding user response：

This can probably help you:

DECLARE @date DATETIME2 = '2021-07-25 06:44'
DECLARE @seconds INT = DATEDIFF(second, CAST(@date AS date), @date)
DECLARE @secondsFromEnd FLOAT = 86400 - @seconds
SELECT @secondsFromEnd / 86400

CodePudding user response：

The Excel formula is returning the difference between the datetime in cell K4 & the start of the next day (i.e. 7/26/2021 00:00) as a fraction of a whole day. The following is the equivalent in T-SQL:

DECLARE @Punch_Start datetime2 = '7/25/2021 06:44';

SELECT DATEDIFF(
    MINUTE,
    @Punch_Start, 
    CAST(
        CAST(
            DATEADD(DAY, 1, @Punch_Start)
        AS date) -- Add 1 day to @Punch_Start & cast as date to remove the time component - this is the start of the next day
    AS datetime2) -- Cast back to datetime2 to get the difference in minutes
) / 1440.; -- Divide the difference in minutes by the number of minutes in a day (60 minutes per hour, 24 hours per day) to get the difference as a fraction of a day