SQL Server

Compute Difference Between Two DateTime Strings

Demonstrates how to compute the difference between two datetime strings. SQL Server

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- Imagine we have these strings:

    DECLARE @t1 nvarchar(4000)
    SELECT @t1 = '2022-11-14 15:45:38'
    DECLARE @t2 nvarchar(4000)
    SELECT @t2 = '2022-11-16 17:23:41'

    -- How many minutes are between the two?

    -- First, we'd want load each date/time into a Chilkat CkDateTime object.
    -- In this case, there are no CkDateTime methods that accept the above format.
    -- However.. the CkDateTime's SetFromTimestamp will accept a date in the format "YYYY-MM-DDThh:mm:ssZ"

    -- First modify the above dates to the timestamp format.
    DECLARE @sb1 int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sb1 OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int
    EXEC sp_OAMethod @sb1, 'Append', @success OUT, @t1
    DECLARE @count int
    EXEC sp_OAMethod @sb1, 'Replace', @count OUT, ' ', 'T'
    EXEC sp_OAMethod @sb1, 'Append', @success OUT, 'Z'

    DECLARE @sb2 int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sb2 OUT

    EXEC sp_OAMethod @sb2, 'Append', @success OUT, @t2
    EXEC sp_OAMethod @sb2, 'Replace', @count OUT, ' ', 'T'
    EXEC sp_OAMethod @sb2, 'Append', @success OUT, 'Z'

    -- Load each into a CkDateTime
    DECLARE @dt1 int
    EXEC @hr = sp_OACreate 'Chilkat.CkDateTime', @dt1 OUT

    EXEC sp_OAMethod @sb1, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @dt1, 'SetFromTimestamp', @success OUT, @sTmp0
    -- verify...
    EXEC sp_OAMethod @dt1, 'GetAsTimestamp', @sTmp0 OUT, 0
    PRINT @sTmp0

    DECLARE @dt2 int
    EXEC @hr = sp_OACreate 'Chilkat.CkDateTime', @dt2 OUT

    EXEC sp_OAMethod @sb2, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @dt2, 'SetFromTimestamp', @success OUT, @sTmp0
    -- verify...
    EXEC sp_OAMethod @dt2, 'GetAsTimestamp', @sTmp0 OUT, 0
    PRINT @sTmp0

    -- Get the difference in seconds
    DECLARE @diffSeconds int
    EXEC sp_OAMethod @dt2, 'DiffSeconds', @diffSeconds OUT, @dt1

    PRINT 'Difference in seconds: ' + @diffSeconds

    PRINT 'Difference in minutes: ' + @diffSeconds / 60

    EXEC @hr = sp_OADestroy @sb1
    EXEC @hr = sp_OADestroy @sb2
    EXEC @hr = sp_OADestroy @dt1
    EXEC @hr = sp_OADestroy @dt2


END
GO