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

Question

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.

BTW a simpler Excel Formula: `=INT(K4+1)-K4` or `=1-MOD(K4,1)` — Scott Craner, Sep 21 '21 at 18:17

score 1 · Answer 1 · answered Sep 21 '21 at 18:28

1

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

answered Sep 21 '21 at 18:28

Kristofer

675
7
15

1

Thank you for your help! – Java Sep 21 '21 at 22:50

score 1 · Accepted Answer · answered Sep 21 '21 at 18:43

1

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

answered Sep 21 '21 at 18:43

Deirdre O'Leary

420
2
6

I have a question. I see you added a period after 1440. I am guessing it is for number with decimal.. Is this a common practice? – Java Sep 21 '21 at 23:11
1

Division of 2 integers in SQL Server will result in an integer unless you cast as a decimal. By adding the period after 1440, I've indicated that this is a decimal, therefore the result is not an integer & is instead determined by data type precedence: https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql I believe this approach is reasonably common, but others may use different methods, e.g. explicitly casting as decimal. – Deirdre O'Leary Sep 22 '21 at 19:26

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

2 Answers2