使用记号的TSQL DATEADD

本文关键字：TSQL DATEADD 记号 | 更新日期: 2023-09-27 18:19:41

上下文

在表值函数中，返回的"表"是从SELECT填充到此表的：

CREATE TABLE [org].[work_schedule_time](
   [id] [int] IDENTITY(1,1) NOT NULL,
   [fk_schedule] [int] NOT NULL,
   [date_start] [datetime] NOT NULL,
   [date_end] [datetime] NOT NULL,
   [is_repeating] [bit] NOT NULL,
   [repeat_interval] [bigint] NULL,
   [repeat_count] [int] NOT NULL,
   [is_available] [bit] NOT NULL,
 CONSTRAINT [PK_work_schedule_time] PRIMARY KEY CLUSTERED 
(
   [id] ASC
)

该函数有三个参数：

  1. @fkSchedule
  2. CCD_ 2和
  3. 日期边界的@dateMax

并返回一个具有正确填充的日期范围的表。例如，对于这一行

id  fk_schedule  date_start               date_end                 is_repeating  repeat_interval  repeat_count  is_available
3   1            2013-06-03 08:00:00.000  2013-06-03 17:00:00.000  1             6048000000000    0             1

调用类似的函数

SELECT * FROM [org].[work_schedule_time_fn](1, '2013-06-01', '2013-07-01')

应该返回类似的表格

schedule_id  date_start               date_end
1            2013-06-03 08:00:00.000  2013-06-03 17:00:00.000
1            2013-06-10 08:00:00.000  2013-06-10 17:00:00.000
1            2013-06-17 08:00:00.000  2013-06-17 17:00:00.000
1            2013-06-24 08:00:00.000  2013-06-24 17:00:00.000

问题

列repeat_interval是表示C#TimeSpan的bigint。（表中已经有数据，并且应用程序逻辑已经在使用中。）

我需要执行这个调用DATEADD(ms, @repeatInterval / 1000, @dateStart)，但我得到了一个Arithmetic overflow error converting expression to data type int。

我读过几篇关于它的帖子，但有些答案建议减去552877920000000000或其他一些神奇的数字，或其他一些巫毒技巧。

那么，如果我有一个包含一些记号的bigint值，我需要将其添加到datetime列中，那么最正确的方法是什么？

谢谢。

最终解决方案

为了避免在蜱虫数量过多时再次溢出，我最终添加了分钟、秒和毫秒。

SET @dateStart = DATEADD(ms, (@repeatInterval % 10000000) / 10000, DATEADD(s, CAST(@repeatInterval / 10000000 as int) % 60, DATEADD(n, CAST(@repeatInterval / 600000000 as int), @dateStart)));

使用记号的TSQL DATEADD

我认为问题在于dateadd()函数采用int参数，并且除法的结果对于int来说太大。

你可以试试这个：

select dateadd(ms, (@repeatInterval%10000000)/10000 , DATEADD(s, cast(@repeatInterval / 10000000 as int), @dateStart))

也就是说，首先加上"秒"，然后加上毫秒。

如果秒数仍然太大，则可以移动到越来越大的时间范围。

问题出在@repeatInterval/1000上。我相信这将是浮动（从内存）

我用这个开始工作了，把除法的结果放回一个bigint。您可能需要先考虑四舍五入，因为普通铸造的结果可能会导致不准确。

declare @dateStart dateTime
declare @repeatInterval bigint
set @dateStart = GetDate()
set @repeatInterval = 30000
select @dateStart, DATEADD(millisecond, cast((@repeatInterval / 1000) as bigint), @dateStart)

选择的答案虽然在大多数情况下完全有效且可用，但会失去潜在的精度和规模。即使刻度精确到100ns，刻度也会被截断为毫秒，并且当时间跨度超过68年时，表达式仍然会溢出。两者都可以通过从毫秒切换到纳秒，并在几天内添加第三个日期添加级别来避免。我知道这太夸张了，但下面的表达式几乎适用于任何可以用sql server数据类型表达的持续时间。

Declare @sec bigint = 10000000;
Declare
    -- Our starting point in time.
    @Moment datetimeoffset = N'1000-01-01 00:00:00.0000000+02:00'
    -- Several durations stored as ticks
,   @TicksSmall bigint = 1234567
,   @TicksMedium bigint =
        (12 * @sec * 60) + (34 * @sec)
    +   1234567
,   @TicksBig bigint =
        (DateDiff( Day, N'1000-01-01', N'1000-12-12' ) * @sec * 60 * 60 * 24)
    +   (12 * @sec * 60 * 60) + (34 * @sec * 60) + (56 * @sec)
    +   1234567
,   @TicksHuge bigint =
        (DateDiff( Day, N'1000-01-01', N'9999-12-12' ) * @sec * 60 * 60 * 24)
    +   (12 * @sec * 60 * 60) + (34 * @sec * 60) + (56 * @sec)
    +   1234567
;
Select
    @TicksSmall As Ticks
,   DateAdd( nanosecond, Convert( int, (@TicksSmall % 10000000) * 100 )
    ,   DateAdd( second, Convert( int, (@TicksSmall % 864000000000) / 10000000 )
        ,   DateAdd( day, Convert( int, @TicksSmall / 864000000000 ), @Moment )
        )
    ) As Moment
Union all
Select
    @TicksMedium
,   DateAdd( nanosecond, Convert( int, (@TicksMedium % 10000000) * 100 )
    ,   DateAdd( second, Convert( int, (@TicksMedium % 864000000000) / 10000000 )
        ,   DateAdd( day, Convert( int, @TicksMedium / 864000000000 ), @Moment )
        )
    )
Union all
Select
    @TicksBig
,   DateAdd( nanosecond, Convert( int, (@TicksBig % 10000000) * 100 )
    ,   DateAdd( second, Convert( int, (@TicksBig % 864000000000) / 10000000 )
        ,   DateAdd( day, Convert( int, @TicksBig / 864000000000 ), @Moment )
        )
    )
Union all
Select
    @TicksHuge
,   DateAdd( nanosecond, Convert( int, (@TicksHuge % 10000000) * 100 )
    ,   DateAdd( second, Convert( int, (@TicksHuge % 864000000000) / 10000000 )
        ,   DateAdd( day, Convert( int, @TicksHuge / 864000000000 ), @Moment )
        )
    )
;
相关文章：