What is the correct SQL type to store a .Net Timespan with values > 24:00:00?

I am trying to store a .Net TimeSpan in SQL server 2008 R2.

EF Code First seems to be suggesting it should be stored as a Time(7) in SQL.

  • Azure SQL query slow
  • Select all rows for the first N distinct child table rows
  • Get Value from another column but same row in SQL
  • Replacing Id with Actual values in SQL Server
  • Deploying SQL CLR Project fails when creating assembly on database
  • SQL Server: how can I know my stored procedure is optimum in performance
  • However TimeSpan in .Net can handle longer periods than 24 hours.

    What is the best way to handle storing .Net TimeSpan in SQL server?

  • Looking for MANAGE button in Azure SQL Databases
  • ASP.NET and SqlDataAdapter with string value
  • Call Stored Procedure with Null Data Parameter in SQL Server
  • SQL - similar data in column
  • .Net vs SSIS: What should SSIS be used for?
  • No access to databases after login
  • 7 Solutions collect form web for “What is the correct SQL type to store a .Net Timespan with values > 24:00:00?”

    I’d store it in the database as a BIGINT and I’d store the number of ticks (eg. TimeSpan.Ticks property).

    That way, if I wanted to get a TimeSpan object when I retrieve it, I could just do TimeSpan.FromTicks(value) which would be easy.

    Thanks for the advice. As there is no equivalent in SQL server. I simply created a 2nd field which converted the TimeSpan to ticks and stored that in the DB. I then prevented storing the TimeSpan

    public Int64 ValidityPeriodTicks { get; set; }
    
    [NotMapped]
    public TimeSpan ValidityPeriod
    {
        get { return TimeSpan.FromTicks(ValidityPeriodTicks); }
        set { ValidityPeriodTicks = value.Ticks; }
    }
    

    If you don’t have to store more than 24 hours you can just store time, since SQL Server 2008 and later the mapping is

    time (SQL Server) <-> TimeSpan(.NET)

    No conversions needed if you only need to store 24 hours or less.

    Source: http://msdn.microsoft.com/en-us/library/cc716729(v=vs.110).aspx

    But, if you want to store more than 24h, you are going to need to store it in ticks, retrieve the data and then convert to TimeSpan. For example

    int timeData = yourContext.yourTable.FirstOrDefault();
    TimeSpan ts = TimeSpan.FromMilliseconds(timeData);
    

    There isn’t a direct equivalent. Just store it numerically, e.g. number of seconds or something appropriate to your required accuracy.

    I know this is an old question, but I wanted to make sure a couple of other options are noted.

    Since you can’t store a TimeSpan greater than 24 hours in a time sql datatype field; a couple of other options might be.

    1. Use a varchar(xx) to store the ToString of the TimeSpan. The benefit of this is the precision doesn’t have to be baked into the datatype or the calculation, (seconds vs milliseconds vs days vs fortnights) All you need to to is use TimeSpan.Parse/TryParse. This is what I would do.

    2. Use a second date, datetime or datetimeoffset, that stores the result of first date + timespan. Reading from the db is a matter of TimeSpan x = SecondDate – FirstDate. Using this option will protect you for other non .NET data access libraries access the same data but not understanding TimeSpans; in case you have such an environment.

    To be consistent with what is probably the most likely source of generating a time span (computing the difference of 2 times or date-times), you may want to store a .NET TimeSpan as a SQL Server DateTime Type.

    This is because in SQL Server, the difference of 2 DateTime‘s (Cast to Float‘s and then Cast back to a DateTime) is simply a DateTime relative to Jan. 1, 1900. Ex. A difference of +0.1 second would be January 1, 1900 00:00:00.100 and -0.1 second would be Dec. 31, 1899 23:59:59.900.

    To convert a .NET TimeSpan to a SQL Server DateTime Type, you would first convert it to a .NET DateTime Type by adding it to a DateTime of Jan. 1, 1900. Of course, when you read it into .NET from SQL Server, you would first read it into a .NET DateTime and then subtract Jan. 1, 1900 from it to convert it to a .NET TimeSpan.

    For use cases where the time spans are being generated from SQL Server DateTime‘s and within SQL Server (i.e. via T-SQL) and SQL Server is prior to 2016, depending on your range and precision needs, it may not be practical to store them as milliseconds (not to mention Ticks) because the Int Type returned by DateDiff (vs. the BigInt from SS 2016+’s DateDiff_Big) overflows after ~24 days worth of milliseconds and ~67 yrs. of seconds. Whereas, this solution will handle time spans with precision down to 0.1 seconds and from -147 to +8,099 yrs..

    WARNINGS:

    1. This would only work if the difference relative to Jan. 1, 1900 would result in a value within the range of a SQL Server DateTime Type (Jan. 1, 1753 to Dec. 31, 9999 aka -147 to +8,099 yrs.). We don’t have to worry near as much on the .NET TimeSpan side, since it can hold ~29 k to +29 k yrs. I didn’t mention the SQL Server DateTime2 Type (whose range, on the negative side, is much greater than SQL Server DateTime‘s), because: a) it cannot be converted to a numeric via a simple Cast and b) DateTime‘s range should suffice for the vast majority of use cases.

    2. SQL Server DateTime differences computed via the Cast – to – Float – and – back method does not appear to be accurate beyond 0.1 seconds.

    Typically, I store a TimeSpan as a bigint populated with ticks from the TimeSpan.Ticks property as previously suggested. You can also store a TimeSpan as a varchar(26) populated with the output of TimeSpan.ToString(). The four scalar functions (ConvertFromTimeSpanString, ConvertToTimeSpanString, DateAddTicks, DateDiffTicks) that I wrote are helpful for handling TimeSpan on the SQL side and avoid the hacks that would produce artificially bounded ranges. If you can store the interval in a .NET TimeSpan at all it should work with these functions also. Additionally, the functions allow you to work with TimeSpans and 100-nanosecond ticks even when using technologies that don’t include the .NET Framework.

    DROP FUNCTION [dbo].[DateDiffTicks]
    GO
    
    DROP FUNCTION [dbo].[DateAddTicks]
    GO
    
    DROP FUNCTION [dbo].[ConvertToTimeSpanString]
    GO
    
    DROP FUNCTION [dbo].[ConvertFromTimeSpanString]
    GO
    
    SET ANSI_NULLS OFF
    GO
    
    SET QUOTED_IDENTIFIER OFF
    GO
    
    -- =============================================
    -- Author:      James Coe
    -- Create date: 2011-05-23
    -- Description: Converts from a varchar(26) TimeSpan string to a bigint containing the number of 100 nanosecond ticks.
    -- =============================================
    /*
        [-][d.]hh:mm:ss[.fffffff] 
    
        "-" 
         A minus sign, which indicates a negative time interval. No sign is included for a positive time span.
    
        "d" 
         The number of days in the time interval. This element is omitted if the time interval is less than one day. 
    
        "hh" 
         The number of hours in the time interval, ranging from 0 to 23. 
    
        "mm" 
         The number of minutes in the time interval, ranging from 0 to 59. 
    
        "ss" 
         The number of seconds in the time interval, ranging from 0 to 59. 
    
        "fffffff" 
         Fractional seconds in the time interval. This element is omitted if the time interval does not include 
         fractional seconds. If present, fractional seconds are always expressed using seven decimal digits.
        */
    CREATE FUNCTION [dbo].[ConvertFromTimeSpanString] (@timeSpan varchar(26))
    RETURNS bigint
    AS
    BEGIN
        DECLARE @hourStart int
        DECLARE @minuteStart int
        DECLARE @secondStart int
        DECLARE @ticks bigint
        DECLARE @hours bigint
        DECLARE @minutes bigint
        DECLARE @seconds DECIMAL(9, 7)
    
        SET @hourStart = CHARINDEX('.', @timeSpan) + 1
        SET @minuteStart = CHARINDEX(':', @timeSpan) + 1
        SET @secondStart = CHARINDEX(':', @timespan, @minuteStart) + 1
        SET @ticks = 0
    
        IF (@hourStart > 1 AND @hourStart < @minuteStart)
        BEGIN
            SET @ticks = CONVERT(bigint, LEFT(@timespan, @hourstart - 2)) * 864000000000
        END
        ELSE
        BEGIN
            SET @hourStart = 1
        END
    
        SET @hours = CONVERT(bigint, SUBSTRING(@timespan, @hourStart, @minuteStart - @hourStart - 1))
        SET @minutes = CONVERT(bigint, SUBSTRING(@timespan, @minuteStart, @secondStart - @minuteStart - 1))
        SET @seconds = CONVERT(DECIMAL(9, 7), SUBSTRING(@timespan, @secondStart, LEN(@timeSpan) - @secondStart + 1))
    
        IF (@ticks < 0)
        BEGIN
            SET @ticks = @ticks - @hours * 36000000000
        END
        ELSE
        BEGIN
            SET @ticks = @ticks + @hours * 36000000000
        END
    
        IF (@ticks < 0)
        BEGIN
            SET @ticks = @ticks - @minutes * 600000000
        END
        ELSE
        BEGIN
            SET @ticks = @ticks + @minutes * 600000000
        END
    
        IF (@ticks < 0)
        BEGIN
            SET @ticks = @ticks - @seconds * 10000000.0
        END
        ELSE
        BEGIN
            SET @ticks = @ticks + @seconds * 10000000.0
        END
    
        RETURN @ticks
    END
    GO
    
    -- =============================================
    -- Author:      James Coe
    -- Create date: 2011-05-23
    -- Description: Converts from a bigint containing the number of 100 nanosecond ticks to a varchar(26) TimeSpan string.
    -- =============================================
    /*
    [-][d.]hh:mm:ss[.fffffff] 
    
    "-" 
     A minus sign, which indicates a negative time interval. No sign is included for a positive time span.
    
    "d" 
     The number of days in the time interval. This element is omitted if the time interval is less than one day. 
    
    "hh" 
     The number of hours in the time interval, ranging from 0 to 23. 
    
    "mm" 
     The number of minutes in the time interval, ranging from 0 to 59. 
    
    "ss" 
     The number of seconds in the time interval, ranging from 0 to 59. 
    
    "fffffff" 
     Fractional seconds in the time interval. This element is omitted if the time interval does not include 
     fractional seconds. If present, fractional seconds are always expressed using seven decimal digits.
    */
    CREATE FUNCTION [dbo].[ConvertToTimeSpanString] (@ticks bigint)
    RETURNS varchar(26)
    AS
    BEGIN
        DECLARE @timeSpanString varchar(26)
    
        IF (@ticks < 0)
        BEGIN
            SET @timeSpanString = '-'
        END
        ELSE
        BEGIN
            SET @timeSpanString = ''
        END
    
        -- Days
        DECLARE @days bigint
    
        SET @days = FLOOR(ABS(@ticks / 864000000000.0))
    
        IF (@days > 0)
        BEGIN
            SET @timeSpanString = @timeSpanString + CONVERT(varchar(26), @days) + '.'
        END
    
        SET @ticks = ABS(@ticks % 864000000000)
        -- Hours
        SET @timeSpanString = @timeSpanString + RIGHT('0' + CONVERT(varchar(26), FLOOR(@ticks / 36000000000.0)), 2) + ':'
        SET @ticks = @ticks % 36000000000
        -- Minutes
        SET @timeSpanString = @timeSpanString + RIGHT('0' + CONVERT(varchar(26), FLOOR(@ticks / 600000000.0)), 2) + ':'
        SET @ticks = @ticks % 600000000
        -- Seconds
        SET @timeSpanString = @timeSpanString + RIGHT('0' + CONVERT(varchar(26), FLOOR(@ticks / 10000000.0)), 2)
        SET @ticks = @ticks % 10000000
    
        -- Fractional Seconds
        IF (@ticks > 0)
        BEGIN
            SET @timeSpanString = @timeSpanString + '.' + LEFT(CONVERT(varchar(26), @ticks) + '0000000', 7)
        END
    
        RETURN @timeSpanString
    END
    GO
    
    -- =============================================
    -- Author:      James Coe
    -- Create date: 2011-05-23
    -- Description: Adds the specified number of 100 nanosecond ticks to a date.
    -- =============================================
    CREATE FUNCTION [dbo].[DateAddTicks] (
        @ticks bigint
        , @starting_date datetimeoffset
        )
    RETURNS datetimeoffset
    AS
    BEGIN
        DECLARE @dateTimeResult datetimeoffset
    
        IF (@ticks < 0)
        BEGIN
            -- Hours
            SET @dateTimeResult = DATEADD(HOUR, CEILING(@ticks / 36000000000.0), @starting_date)
            SET @ticks = @ticks % 36000000000
            -- Seconds
            SET @dateTimeResult = DATEADD(SECOND, CEILING(@ticks / 10000000.0), @dateTimeResult)
            SET @ticks = @ticks % 10000000
            -- Nanoseconds
            SET @dateTimeResult = DATEADD(NANOSECOND, @ticks * 100, @dateTimeResult)
        END
        ELSE
        BEGIN
            -- Hours
            SET @dateTimeResult = DATEADD(HOUR, FLOOR(@ticks / 36000000000.0), @starting_date)
            SET @ticks = @ticks % 36000000000
            -- Seconds
            SET @dateTimeResult = DATEADD(SECOND, FLOOR(@ticks / 10000000.0), @dateTimeResult)
            SET @ticks = @ticks % 10000000
            -- Nanoseconds
            SET @dateTimeResult = DATEADD(NANOSECOND, @ticks * 100, @dateTimeResult)
        END
    
        RETURN @dateTimeResult
    END
    GO
    
    -- =============================================
    -- Author:      James Coe
    -- Create date: 2011-05-23
    -- Description:  Gets the difference between two dates in 100 nanosecond ticks.
    -- =============================================
    CREATE FUNCTION [dbo].[DateDiffTicks] (
        @starting_date datetimeoffset
        , @ending_date datetimeoffset
        )
    RETURNS bigint
    AS
    BEGIN
        DECLARE @ticks bigint
        DECLARE @days bigint
        DECLARE @hours bigint
        DECLARE @minutes bigint
        DECLARE @seconds bigint
    
        SET @hours = DATEDIFF(HOUR, @starting_date, @ending_date)
        SET @starting_date = DATEADD(HOUR, @hours, @starting_date)
        SET @ticks = @hours * 36000000000
        SET @seconds = DATEDIFF(SECOND, @starting_date, @ending_date)
        SET @starting_date = DATEADD(SECOND, @seconds, @starting_date)
        SET @ticks = @ticks + @seconds * 10000000
        SET @ticks = @ticks + CONVERT(bigint, DATEDIFF(NANOSECOND, @starting_date, @ending_date)) / 100
    
        RETURN @ticks
    END
    GO
    
    --- BEGIN Test Harness ---
    SET NOCOUNT ON
    
    DECLARE @dateTimeOffsetMinValue datetimeoffset
    DECLARE @dateTimeOffsetMaxValue datetimeoffset
    DECLARE @timeSpanMinValueString varchar(26)
    DECLARE @timeSpanZeroString varchar(26)
    DECLARE @timeSpanMaxValueString varchar(26)
    DECLARE @timeSpanMinValueTicks bigint
    DECLARE @timeSpanZeroTicks bigint
    DECLARE @timeSpanMaxValueTicks bigint
    DECLARE @dateTimeOffsetMinMaxDiffTicks bigint
    DECLARE @dateTimeOffsetMaxMinDiffTicks bigint
    
    SET @dateTimeOffsetMinValue = '0001-01-01T00:00:00.0000000+00:00'
    SET @dateTimeOffsetMaxValue = '9999-12-31T23:59:59.9999999+00:00'
    SET @timeSpanMinValueString = '-10675199.02:48:05.4775808'
    SET @timeSpanZeroString = '00:00:00'
    SET @timeSpanMaxValueString = '10675199.02:48:05.4775807'
    SET @timeSpanMinValueTicks = -9223372036854775808
    SET @timeSpanZeroTicks = 0
    SET @timeSpanMaxValueTicks = 9223372036854775807
    SET @dateTimeOffsetMinMaxDiffTicks = 3155378975999999999
    SET @dateTimeOffsetMaxMinDiffTicks = -3155378975999999999
    
    -- TimeSpan Conversion Tests
    PRINT 'Testing TimeSpan conversions...'
    
    DECLARE @convertToTimeSpanStringMinTicksResult varchar(26)
    DECLARE @convertFromTimeSpanStringMinTimeSpanResult bigint
    DECLARE @convertToTimeSpanStringZeroTicksResult varchar(26)
    DECLARE @convertFromTimeSpanStringZeroTimeSpanResult bigint
    DECLARE @convertToTimeSpanStringMaxTicksResult varchar(26)
    DECLARE @convertFromTimeSpanStringMaxTimeSpanResult bigint
    
    SET @convertToTimeSpanStringMinTicksResult = dbo.ConvertToTimeSpanString(@timeSpanMinValueTicks)
    SET @convertFromTimeSpanStringMinTimeSpanResult = dbo.ConvertFromTimeSpanString(@timeSpanMinValueString)
    SET @convertToTimeSpanStringZeroTicksResult = dbo.ConvertToTimeSpanString(@timeSpanZeroTicks)
    SET @convertFromTimeSpanStringZeroTimeSpanResult = dbo.ConvertFromTimeSpanString(@timeSpanZeroString)
    SET @convertToTimeSpanStringMaxTicksResult = dbo.ConvertToTimeSpanString(@timeSpanMaxValueTicks)
    SET @convertFromTimeSpanStringMaxTimeSpanResult = dbo.ConvertFromTimeSpanString(@timeSpanMaxValueString)
    
    -- Test Results
    SELECT 'Convert to TimeSpan String from Ticks (Minimum)' AS Test
        , CASE 
            WHEN @convertToTimeSpanStringMinTicksResult = @timeSpanMinValueString
                THEN 'Pass'
            ELSE 'Fail'
            END AS [Test Status]
        , @timeSpanMinValueTicks AS [Ticks]
        , CONVERT(varchar(26), NULL) AS [TimeSpan String]
        , CONVERT(varchar(26), @convertToTimeSpanStringMinTicksResult) AS [Actual Result]
        , CONVERT(varchar(26), @timeSpanMinValueString) AS [Expected Result]
    UNION ALL
    SELECT 'Convert from TimeSpan String to Ticks (Minimum)' AS Test
        , CASE 
            WHEN @convertFromTimeSpanStringMinTimeSpanResult = @timeSpanMinValueTicks
                THEN 'Pass'
            ELSE 'Fail'
            END AS [Test Status]
        , NULL AS [Ticks]
        , @timeSpanMinValueString AS [TimeSpan String]
        , CONVERT(varchar(26), @convertFromTimeSpanStringMinTimeSpanResult) AS [Actual Result]
        , CONVERT(varchar(26), @timeSpanMinValueTicks) AS [Expected Result]
    UNION ALL
    SELECT 'Convert to TimeSpan String from Ticks (Zero)' AS Test
        , CASE 
            WHEN @convertToTimeSpanStringZeroTicksResult = @timeSpanZeroString
                THEN 'Pass'
            ELSE 'Fail'
            END AS [Test Status]
        , @timeSpanZeroTicks AS [Ticks]
        , CONVERT(varchar(26), NULL) AS [TimeSpan String]
        , CONVERT(varchar(26), @convertToTimeSpanStringZeroTicksResult) AS [Actual Result]
        , CONVERT(varchar(26), @timeSpanZeroString) AS [Expected Result]
    UNION ALL
    SELECT 'Convert from TimeSpan String to Ticks (Zero)' AS Test
        , CASE 
            WHEN @convertFromTimeSpanStringZeroTimeSpanResult = @timeSpanZeroTicks
                THEN 'Pass'
            ELSE 'Fail'
            END AS [Test Status]
        , NULL AS [Ticks]
        , @timeSpanZeroString AS [TimeSpan String]
        , CONVERT(varchar(26), @convertFromTimeSpanStringZeroTimeSpanResult) AS [Actual Result]
        , CONVERT(varchar(26), @timeSpanZeroTicks) AS [Expected Result]
    UNION ALL
    SELECT 'Convert to TimeSpan String from Ticks (Maximum)' AS Test
        , CASE 
            WHEN @convertToTimeSpanStringMaxTicksResult = @timeSpanMaxValueString
                THEN 'Pass'
            ELSE 'Fail'
            END AS [Test Status]
        , @timeSpanMaxValueTicks AS [Ticks]
        , CONVERT(varchar(26), NULL) AS [TimeSpan String]
        , CONVERT(varchar(26), @convertToTimeSpanStringMaxTicksResult) AS [Actual Result]
        , CONVERT(varchar(26), @timeSpanMaxValueString) AS [Expected Result]
    UNION ALL
    SELECT 'Convert from TimeSpan String to Ticks (Maximum)' AS Test
        , CASE 
            WHEN @convertFromTimeSpanStringMaxTimeSpanResult = @timeSpanMaxValueTicks
                THEN 'Pass'
            ELSE 'Fail'
            END AS [Test Status]
        , NULL AS [Ticks]
        , @timeSpanMaxValueString AS [TimeSpan String]
        , CONVERT(varchar(26), @convertFromTimeSpanStringMaxTimeSpanResult) AS [Actual Result]
        , CONVERT(varchar(26), @timeSpanMaxValueTicks) AS [Expected Result]
    
    -- Ticks Date Add Test
    PRINT 'Testing DateAddTicks...'
    
    DECLARE @DateAddTicksPositiveTicksResult datetimeoffset
    DECLARE @DateAddTicksZeroTicksResult datetimeoffset
    DECLARE @DateAddTicksNegativeTicksResult datetimeoffset
    
    SET @DateAddTicksPositiveTicksResult = dbo.DateAddTicks(@dateTimeOffsetMinMaxDiffTicks, @dateTimeOffsetMinValue)
    SET @DateAddTicksZeroTicksResult = dbo.DateAddTicks(@timeSpanZeroTicks, @dateTimeOffsetMinValue)
    SET @DateAddTicksNegativeTicksResult = dbo.DateAddTicks(@dateTimeOffsetMaxMinDiffTicks, @dateTimeOffsetMaxValue)
    
    -- Test Results
    SELECT 'Date Add with Ticks Test (Positive)' AS Test
        , CASE 
            WHEN @DateAddTicksPositiveTicksResult = @dateTimeOffsetMaxValue
                THEN 'Pass'
            ELSE 'Fail'
            END AS [Test Status]
        , @dateTimeOffsetMinMaxDiffTicks AS [Ticks]
        , @dateTimeOffsetMinValue AS [Starting Date]
        , @DateAddTicksPositiveTicksResult AS [Actual Result]
        , @dateTimeOffsetMaxValue AS [Expected Result]
    UNION ALL
    SELECT 'Date Add with Ticks Test (Zero)' AS Test
        , CASE 
            WHEN @DateAddTicksZeroTicksResult = @dateTimeOffsetMinValue
                THEN 'Pass'
            ELSE 'Fail'
            END AS [Test Status]
        , @timeSpanZeroTicks AS [Ticks]
        , @dateTimeOffsetMinValue AS [Starting Date]
        , @DateAddTicksZeroTicksResult AS [Actual Result]
        , @dateTimeOffsetMinValue AS [Expected Result]
    UNION ALL
    SELECT 'Date Add with Ticks Test (Negative)' AS Test
        , CASE 
            WHEN @DateAddTicksNegativeTicksResult = @dateTimeOffsetMinValue
                THEN 'Pass'
            ELSE 'Fail'
            END AS [Test Status]
        , @dateTimeOffsetMaxMinDiffTicks AS [Ticks]
        , @dateTimeOffsetMaxValue AS [Starting Date]
        , @DateAddTicksNegativeTicksResult AS [Actual Result]
        , @dateTimeOffsetMinValue AS [Expected Result]
    
    -- Ticks Date Diff Test
    PRINT 'Testing Date Diff Ticks...'
    
    DECLARE @dateDiffTicksMinMaxResult bigint
    DECLARE @dateDiffTicksMaxMinResult bigint
    
    SET @dateDiffTicksMinMaxResult = dbo.DateDiffTicks(@dateTimeOffsetMinValue, @dateTimeOffsetMaxValue)
    SET @dateDiffTicksMaxMinResult = dbo.DateDiffTicks(@dateTimeOffsetMaxValue, @dateTimeOffsetMinValue)
    
    -- Test Results
    SELECT 'Date Difference in Ticks Test (Min, Max)' AS Test
        , CASE 
            WHEN @dateDiffTicksMinMaxResult = @dateTimeOffsetMinMaxDiffTicks
                THEN 'Pass'
            ELSE 'Fail'
            END AS [Test Status]
        , @dateTimeOffsetMinValue AS [Starting Date]
        , @dateTimeOffsetMaxValue AS [Ending Date]
        , @dateDiffTicksMinMaxResult AS [Actual Result]
        , @dateTimeOffsetMinMaxDiffTicks AS [Expected Result]
    UNION ALL
    SELECT 'Date Difference in Ticks Test (Max, Min)' AS Test
        , CASE 
            WHEN @dateDiffTicksMaxMinResult = @dateTimeOffsetMaxMinDiffTicks
                THEN 'Pass'
            ELSE 'Fail'
            END AS [Test Status]
        , @dateTimeOffsetMaxValue AS [Starting Date]
        , @dateTimeOffsetMinValue AS [Ending Date]
        , @dateDiffTicksMaxMinResult AS [Actual Result]
        , @dateTimeOffsetMaxMinDiffTicks AS [Expected Result]
    
    PRINT 'Tests Complete.'
    GO
    --- END Test Harness ---
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.