在SQL Server中创建一个持续时间函数 [英] Creating a Duration Function in SQL Server

问题描述

我正在尝试创建一个函数，该函数将返回两个日期之间已经过去的整个周期数，而不是跨越的边界数。

举例myFunc（小时，'01：31'，'03:20'）= 1（在03:31将变为2）

>

我遇到3个问题：

1. 日期计算很复杂，我希望保持我的代码整洁，尽可能简单，所以我想使用内置日期函数而不是重新创建公历。

7. b $ b

8. 我需要在select语句中调用它，因此它不能是存储过程，但SQL中的函数会拒绝运行动态SQL或存储过程。




9. （自从我运行SSMS 2012以来，我遇到了一些重载DateDiff的问题，因此没有DateDiff_BIG）

以下是我的代码：

  [dbo]。[DDif] 
（
 @Start datetime 
，@ End datetime 
，@ Period varchar = null 
）
 RETURNS bigint 
声明@Dif bigint 
如果@Period为空set @Period ='Y'
 @p varchar（20）= case $ b $ when @Period ='Y'then'yy'
 @Period in（'S ''，'Q'）然后'q'
 @Period ='M'然后'm'
当@Period在（'B'，'W'）然后'ww'
当@Period ='D 'then'd'
 @Period ='h'然后'hh'
 @Period ='m'然后'n'
当@Period ='s'时''''' 
 End 
 @sQry varchar（8000）='设置@Dif = DateDiff（'+ @ p +'，@ Start，@ End）+ DateAdd（'+ @ p +'，DateDiff（'+ @ （'S'，'B'）中的@Period返回@sQry 
返回-1结束'
执行@sQry 
返回@ Dif / 2 
 return @Dif 
  

我正在寻找3种解决方案中的任何一种：

1. 将dateparts作为变量传递的非动态方式

一种从函数中运行动态SQL的方法


2. 干净/简单地实现一种不同的方法来计算用户指定的两个日期时间之间的持续时间周期长度。
   

注意：必须删除堆栈溢出不喜欢的一小部分代码，所以我知道它不会像现在这样运行。

编辑：
感谢Mark解决方案。这是我当前的实现：

  FUNCTION [dbo]。[DDif] 
（
 @Start datetime 
，@结束日期时间
，@期间varchar =空白
）
 RETURNS bigint 
 AS 
 BEGIN 
如果@Period为空set @期间='Y'
声明@m int = DATEDIFF（Month，@ Start，@ End）+ DATEADD（MONTH，DATEDIFF（Month，@ Start，@ End），@ Start）时的情况> @End then -1 else 0 END 
 declare @s bigint = 86400 * DATEDIFF（DAY，CAST（@ Start as date），CAST（@End as date））+ datediff（s，cast（@Start as time），cast （@End as time））
 declare @num bigint = case $ b $ when @Period ='Y'then 12 
 @Period ='S'then 6 
 @Period ='Q'然后3 @ b $ b @Period ='M'然后1 
 @Period ='B'然后1209600 
 @Period ='W'然后604800 
当@Period ='D'然后86400 
 @Period ='h'然后3600 
 @Period ='m'然后60 
 when @Period ='s'then 1 
 end 
 return case b $ b when @Period in（'Y'，'S'，'Q'，'M'）then @m （'B'，'W'，'D'，'h'，'m'，'s'）中@Period时的
然后@s 
 end / @num 
  




解决方案

您可以调用 DateDiff 日期部分对应于时间戳中最小的有意义的精度，然后进行分割以获得正确单位的时间间隔。

例如，如果您将时间缩短到第二，获取小时数 DateDiff ，其中日期部分为 ss ，然后除以3600。的想法是，如果你的时间只是精确到秒，那么越过1秒的边界就意味着与所有可测量目的的花费1秒相同，然后你转换为你真正需要的单位。

如果你的时间间隔很长，你的时间戳精度很高 - 例如如果您需要使用微秒级的精度进行为期一年的时间间隔 - 您可能需要调用DateDiff_Big。

更新

如果您需要更长的时间间隔的更高精度和不能使用DateDiff_Big，那么您必须稍微复杂一些。一种方法是：

首先将时间与时间分开。

然后 date_diff 日期部分，以天为单位。 （这个范围有几百万年）。将结果乘以86,400,000（结果将需要一个64位存储。）

现在 date_diff ms精度的时间。

注意 date_diff 进行了有符号比较，因此后面的差异可能是正值或负值。通过乘以日期部分的差异，将它添加到你得到的部分，现在你有一个精确的计数ms。

分割获得你想要的单位。 / p>

更新2 - 我原本声称纳秒可行，但那是因为我无法正确移动小数点。

I am trying to create a function which will return the whole number of periods that have elapsed between two dates, NOT the number of boundaries crossed.

As an example myFunc(Hour, '01:31','03:20') = 1 (would become 2 at "03:31")

I am running into 3 issues:

1. Date calculations are complicated and I would like to keep my code neat and as simple as possible, so I'd like to use built-in date function instead of reinventing the Gregorian calendar.

2. DateDiff takes a datepart argument, which has resisted all my attempts to pass as a variable, except via dynamic sql.

3. I need to call this in a select statement so it can't be a stored procedure, but functions in SQL refuse to run either dynamic SQL or stored procedures.

4. (I ran into a couple issues with overloading DateDiff since I am running SSMS 2012 and therefore don't have DateDiff_BIG)

Here is my code:

    [dbo].[DDif]
(
    @Start datetime
    ,@End datetime
    ,@Period varchar = null
)
RETURNS bigint
Declare @Dif bigint
if @Period is null set @Period = 'Y'
@p varchar(20) = case
    when @Period = 'Y' then 'yy'
    when @Period in('S','Q') then 'q'
    when @Period = 'M' then 'm'
    when @Period in('B','W') then 'ww'
    when @Period = 'D' then 'd' 
    when @Period = 'h' then 'hh'
    when @Period = 'm' then 'n'
    when @Period = 's' then 's'
End
@sQry varchar(8000) = 'Set @Dif = DateDiff('+@p+',@Start,@End) + case when DateAdd('+@p+',DateDiff('+@p+',@Start,@End),@Start)>@End then -1 else 0 end'
execute @sQry
if @Period in ('S','B') return @Dif/2
return @Dif

I am looking for any one of 3 solutions:

1. A non-dynamic way to pass dateparts as variables

2. A way to run dynamic SQL from a function

3. A clean/simple implementation of a different approach to calculating duration between two datetimes for a user-specified period length.

Note: Had to remove a few small parts of my code that stack overflow didn't like, so I know that it won't run exactly as it appears.

Edit: Thank you to Mark for the solution. Here is my current implementation:

FUNCTION [dbo].[DDif]
(
    @Start datetime
    ,@End datetime
    ,@Period varchar = null
)
RETURNS bigint
AS
BEGIN
    if @Period is null set @Period = 'Y'
    declare @m int = DATEDIFF(Month,@Start,@End) + case when DATEADD(MONTH,DATEDIFF(Month,@Start,@End),@Start)>@End then -1 else 0 END
    declare @s bigint = 86400*DATEDIFF(DAY, CAST(@Start as date),CAST(@End as date))+datediff(s,cast(@Start as time),cast(@End as time))
    declare @num bigint = case 
        when @Period = 'Y' then 12
        when @Period = 'S' then 6
        when @Period = 'Q' then 3
        when @Period = 'M' then 1
        when @Period = 'B' then 1209600
        when @Period = 'W' then 604800
        when @Period = 'D' then 86400
        when @Period = 'h' then 3600
        when @Period = 'm' then 60
        when @Period = 's' then 1
    end
    return case
        when @Period in ('Y','S','Q','M') then @m
        when @Period in ('B','W','D','h','m','s') then @s
    end / @num

解决方案

You can call DateDiff with the date part corresponding to the smallest meaningful precision in your timestamps, and then divide to get the interval in the correct unit.

For example, if you store times down to the second, to get the number of hours call DateDiff with the date part as ss and then divide by 3600. The idea is that if your times are only accurate to the second, then "crossed 1 second boundary" means the same as "took 1 second" for all measurable purposes, and then you just convert to the units you really need.

If your intervals are long and your timestamps are high precision - e.g. if you need to use microsecond precision for year-long intervals - you may have to call DateDiff_Big instead.

UPDATE

If you need higher precision for longer intervals and can't use DateDiff_Big, then you have to get a little trickier. One way would be:

First separate the dates from the times.

Then date_diff the date parts, counting in days. (This has a range of several million years.) Multiply the result by 86,400,000 (result will need a 64-bit storage.)

Now date_diff the times with ms precision.

Note date_diff does a signed comparison, so the latter diff may be positive or negative. Add it to the part you got by multiplying the diff of the date parts, and now you have an accurate count of ms.

Divide to get the units you want.

UPDATE 2 - I originally claimed nanoseconds would work, but that's because I can't move decimal points correctly.

这篇关于在SQL Server中创建一个持续时间函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！