存储值 > 24:00:00 的 .Net 时间跨度的正确 SQL 类型是什么？

Question

提问by GraemeMiller

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

我正在尝试TimeSpan在 SQL Server 2008 R2 中存储 .Net 。

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

EF Code First 似乎暗示它应该Time(7)在 SQL 中存储为 a 。

However TimeSpanin .Net can handle longer periods than 24 hours.

但是TimeSpan在 .Net 中可以处理超过 24 小时的时间。

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

处理TimeSpan在 SQL 服务器中存储 .Net 的最佳方法是什么？

Answer 1

回答by Tom Chantler

I'd store it in the database as a BIGINTand I'd store the number of ticks (eg. TimeSpan.Ticksproperty).

我将它作为 a 存储在数据库中，BIGINT并存储刻度数（例如TimeSpan.Ticks属性）。

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.

这样，如果我想在检索时获得 TimeSpan 对象，我可以只执行TimeSpan.FromTicks(value)，这很容易。

Answer 2

回答by GraemeMiller

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

感谢您的建议。因为在 SQL Server 中没有等价物。我只是创建了第二个字段，它将 TimeSpan 转换为刻度并将其存储在数据库中。然后我阻止存储 TimeSpan

public Int64 ValidityPeriodTicks { get; set; }

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

Answer 3

回答by Alejandro del Río

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

如果您不必存储超过 24 小时，您可以只存储time，因为 SQL Server 2008 和更高版本的映射是

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

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

如果您只需要存储 24 小时或更短时间，则无需转换。

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

来源：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

但是，如果要存储超过 24 小时，则需要将其存储在刻度中，检索数据，然后转换为 TimeSpan。例如

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

Answer 4

回答by fearofawhackplanet

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

没有直接的等价物。只需以数字形式存储它，例如秒数或适合您所需精度的东西。

Answer 5

回答by rick

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.

由于您不能在时间 sql 数据类型字段中存储超过 24 小时的 TimeSpan；其他几个选项可能是。

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.
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.

使用 varchar(xx) 存储 TimeSpan 的 ToString。这样做的好处是不必将精度计入数据类型或计算中，（秒 vs 毫秒 vs 天 vs 两周）您只需要使用 TimeSpan.Parse/TryParse。这就是我要做的。
使用第二个日期、datetime 或 datetimeoffset，它存储第一个日期 + 时间跨度的结果。从数据库中读取是 TimeSpan x = SecondDate - FirstDate 的问题。使用此选项将保护您免受其他非 .NET 数据访问库访问相同数据但不了解 TimeSpans 的影响；如果你有这样的环境。

Answer 6

回答by Tom

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 TimeSpanas a SQL Server DateTimeType.

为了与最有可能生成时间跨度的来源（计算 2 次或日期时间的差异）保持一致，您可能希望将 .NET 存储TimeSpan为 SQL ServerDateTime类型。

This is because in SQL Server, the difference of 2 DateTime's (Castto Float's and then Castback to a DateTime) is simply a DateTimerelative 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.

这是因为在 SQL Server 中， 2 的差异DateTime（Castto Float's 然后Cast回到 a DateTime）只是DateTime相对于 1900 年 1 月 1 日。例如。+0.1 秒的差异将是 1900 年 1 月 1 日 00:00:00.100，-0.1 秒将是 1899 年 12 月 31 日 23:59:59.900。

To convert a .NET TimeSpanto a SQL Server DateTimeType, you would first convert it to a .NET DateTimeType by adding it to a DateTimeof Jan. 1, 1900. Of course, when you read it into .NET from SQL Server, you would first read it into a .NET DateTimeand then subtract Jan. 1, 1900 from it to convert it to a .NET TimeSpan.

要将 .NET 转换为TimeSpanSQL ServerDateTime类型，您首先要DateTime通过将其添加到DateTime1900 年 1 月 1 日的a将其转换为 .NET类型。当然，当您从 SQL Server 将其读入 .NET 时，您首先会将其读入 .NET DateTime，然后从中减去 1900 年 1 月 1 日以将其转换为 .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 IntType returned by DateDiff(vs. the BigIntfrom 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..

对于从 SQL ServerDateTime和 SQL Server 内部（即通过 T-SQL）生成时间跨度并且 SQL Server 在 2016 年之前的用例，根据您的范围和精度需求，存储它们可能不切实际作为毫秒（更不用说Ticks），因为（与来自 SS 2016+ 的）Int返回的类型在大约 24 天的毫秒和大约 67 年后溢出。秒。然而，该解决方案将处理时间跨度，精度低至 0.1 秒，从 -147 到 +8,099 年。DateDiffBigIntDateDiff_Big

WARNINGS:

警告：

This would only work if the difference relative to Jan. 1, 1900 would result in a value within the range of a SQL Server DateTimeType (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 TimeSpanside, since it can hold ~29 k to +29 k yrs. I didn't mention the SQL Server DateTime2Type (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 Castand b) DateTime's range should suffice for the vast majority of use cases.
SQL Server DateTimedifferences computed via the Cast- to - Float- and - back method does not appear to be accurate beyond 0.1 seconds.

仅当相对于 1900 年 1 月 1 日的差异导致 SQL ServerDateTime类型范围内的值（1753 年 1 月 1 日至 9999 年 12 月 31 日，又名 -147 至 +8,099 年）时，这才有效。我们不必担心 .NETTimeSpan方面的问题，因为它可以容纳 ~29 k 到 +29 k 年。我没有提到 SQL ServerDateTime2类型（从消极方面来说，其范围远大于 SQL ServerDateTime的范围），因为：a）它不能通过简单的转换为数字，Cast而 b）DateTime的范围就足够了对于绝大多数用例。
DateTime通过Cast- 到 -Float和 - 返回方法计算的SQL Server差异在 0.1 秒后似乎不准确。

Answer 7

回答by MovGP0

There are multiple ways how to present a timespan in the database.

有多种方法可以在数据库中显示时间跨度。

time

时间

This datatype is supported since SQL Server 2008 and is the prefered wayto store a TimeSpan. There is no mapping needed. It also works well with SQL code.

因为SQL Server 2008中此数据类型支持，是首选的方式来存储TimeSpan。不需要映射。它也适用于 SQL 代码。

public TimeSpan ValidityPeriod { get; set; }

However, as stated in the original question, this datatype is limited to 24 hours.

但是，如原始问题所述，此数据类型限制为 24 小时。

datetimeoffset

日期时间偏移

The datetimeoffsetdatatype maps directly to System.DateTimeOffset. It's used to express the offset between a datetime/datetime2to UTC, but you can also use it for TimeSpan.

该datetimeoffset数据类型直接映射到System.DateTimeOffset。它用于表示datetime/datetime2到 UTC之间的偏移量，但您也可以将它用于TimeSpan.

However, since the datatype suggests a very specific semantic, so you should also consider other options.

但是，由于数据类型暗示了非常具体的语义，因此您还应该考虑其他选项。

datetime / datetime2

日期时间/日期时间2

One approach might be to use the datetimeor datetime2types. This is best in scenarios where you need to process the values in the database directly, ie. for views, stored procedures, or reports. The drawback is that you need to substract the value DateTime(1900,01,01,00,00,00)from the date to get back the timespan in your business logic.

一种方法可能是使用datetimeordatetime2类型。这在您需要直接处理数据库中的值的情况下是最好的，即。用于视图、存储过程或报告。缺点是您需要DateTime(1900,01,01,00,00,00)从日期中减去该值以获取业务逻辑中的时间跨度。

public DateTime ValidityPeriod { get; set; }

[NotMapped]
public TimeSpan ValidityPeriodTimeSpan
{
    get { return ValidityPeriod - DateTime(1900,01,01,00,00,00); }
    set { ValidityPeriod = DateTime(1900,01,01,00,00,00) + value; }
}

bigint

大整数

Another approach might be to convert the TimeSpan into ticks and use the bigintdatatype. However, this approach has the drawback that it's cumbersome to use in SQL queries.

另一种方法可能是将 TimeSpan 转换为刻度并使用bigint数据类型。但是，这种方法的缺点是在 SQL 查询中使用起来很麻烦。

public long ValidityPeriod { get; set; }

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

varchar(N)

变量(N)

This is best for cases where the value should be readable by humans. You might also use this format in SQL queries by utilizing the CONVERT(datetime, ValidityPeriod)function. Dependent on the required precision, you will need between 8 and 25 characters.

这最适合人类可读的值。您还可以通过使用该CONVERT(datetime, ValidityPeriod)函数在 SQL 查询中使用此格式。根据所需的精度，您将需要 8 到 25 个字符。

public string ValidityPeriod { get; set; }

[NotMapped]
public TimeSpan ValidityPeriodTimeSpan
{
    get { return TimeSpan.Parse(ValidityPeriod); }
    set { ValidityPeriod = value.ToString("HH:mm:ss"); }
}

Bonus: Period and Duration

奖励：期间和持续时间

Using a string, you can also store NodaTimedatatypes, especially Durationand Period. The first is basically the same as a TimeSpan, while the later respects that some days and months are longer or shorter than others (ie. January has 31 days and February has 28 or 29; some days are longer or shorter because of daylight saving time). In such cases, using a TimeSpan is the wrong choice.

使用字符串，您还可以存储NodaTime数据类型，尤其是Duration和Period。第一个与 TimeSpan 基本相同，而后者则认为有些日子和月份比其他日子长或短（即 1 月有 31 天，2 月有 28 或 29 天；由于夏令时，有些日子或长或短）。在这种情况下，使用 TimeSpan 是错误的选择。

You can use this code to convert Periods:

您可以使用此代码来转换句点：

using NodaTime;
using NodaTime.Serialization.JsonNet;

internal static class PeriodExtensions
{
    public static Period ToPeriod(this string input)
    {
        var js = JsonSerializer.Create(new JsonSerializerSettings());
        js.ConfigureForNodaTime(DateTimeZoneProviders.Tzdb);
        var quoted = string.Concat(@"""", input, @"""");
        return js.Deserialize<Period>(new JsonTextReader(new StringReader(quoted)));
    }
}

And then use it like

然后像这样使用它

public string ValidityPeriod { get; set; }

[NotMapped]
public Period ValidityPeriodPeriod
{
    get => ValidityPeriod.ToPeriod();
    set => ValidityPeriod = value.ToString();
}

I really like NodaTimeand it often saves me from tricky bugs and lots of headache. The drawback here is that you really can't use it in SQL queries and need to do calculations in-memory.

我真的很喜欢NodaTime它，它经常使我免于棘手的错误和很多头痛。这里的缺点是你真的不能在 SQL 查询中使用它，需要在内存中进行计算。

CLR User-Defined Type

CLR 用户定义类型

You also have the option to use a custom datatype and support a custom TimeSpanclass directly. See CLR User-Defined Typesfor details.

您还可以选择使用自定义数据类型并TimeSpan直接支持自定义类。有关详细信息，请参阅CLR 用户定义类型。

The drawback here is that the datatype might not behave well with SQL Reports. Also, some versions of SQL Server (Azure, Linux, Data Warehouse) are not supported.

这里的缺点是数据类型在 SQL 报告中可能表现不佳。此外，不支持某些版本的 SQL Server（Azure、Linux、数据仓库）。

Value Conversions

价值转换

Starting with EntityFramework Core 2.1, you have the option to use Value Conversions.

从 EntityFramework Core 2.1 开始，您可以选择使用Value Conversions。

However, when using this, EF will not be able to convert many queriesinto SQL, causing queries to run in-memory; potentially transfering lots and lots of data to your application.

但是，在使用这个的时候，EF将无法将很多查询转换成SQL，导致查询在内存中运行；可能会向您的应用程序传输大量数据。

So at least for now, it might be better not to use it, and just map the query result with Automapper.

所以至少现在，最好不要使用它，只用Automapper 映射查询结果。

Answer 8

回答by JamieSee

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.

通常，我将 TimeSpan 存储为一个 bigint，并按照之前的建议填充来自 TimeSpan.Ticks 属性的刻度。您还可以将 TimeSpan 存储为填充有 TimeSpan.ToString() 输出的 varchar(26)。我编写的四个标量函数（ConvertFromTimeSpanString、ConvertToTimeSpanString、DateAddTicks、DateDiffTicks）有助于在 SQL 端处理 TimeSpan 并避免会产生人为限制范围的黑客行为。如果您可以将时间间隔存储在 .NET TimeSpan 中，它也应该与这些函数一起使用。此外，即使在使用不包括 .NET Framework 的技术时，这些函数也允许您使用 TimeSpans 和 100 纳秒刻度。

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 ---

存储值 > 24:00:00 的 .Net 时间跨度的正确 SQL 类型是什么？

提问by GraemeMiller

回答by Tom Chantler

回答by GraemeMiller

回答by Alejandro del Río

回答by fearofawhackplanet

回答by rick

回答by Tom

回答by MovGP0

回答by JamieSee

相关推荐

最近更新

标签

存储值 > 24:00:00 的 .Net 时间跨度的正确 SQL 类型是什么？

提问by GraemeMiller

回答by Tom Chantler

回答by GraemeMiller

回答by Alejandro del Río

回答by fearofawhackplanet

回答by rick

回答by Tom

回答by MovGP0

回答by JamieSee

相关推荐

.net 单元测试项目找不到被测程序集（或依赖项）

.net 使用 https 端点添加 WCF 服务引用

.net 无法加载文件或程序集“AutoMapper”或其依赖项之一

.net 如何记录 WCF 消息内容？

相关推荐

最近更新

标签