计算功能中的日期 [英] Calculation of date in function

问题描述

  1 28/11/2011 ... 
  

如何在SQL中编写函数来实现上述模式？

解决方案

您可以在SQL Server中执行此操作： / p>

  DECLARE @BaseDate DATE ='20111107'; 
 DECLARE @EndDate DATE = GETDATE（）; - 数据库中的日期结束
 WITH RecursiveCTE AS（
 SELECT 
 1 AS [Counter]，
 @BaseDate AS [MyDate] 
 UNION ALL 
 SELECT 
 [Counter] + 1，
 DATEADD（DAY，7，MyDate）
 FROM 
递归CTE 
 WHERE 
 MyDate< @EndDate）
 SELECT * FROM RecursiveCTE OPTION（MAXRECURSION 0）; 
  

要处理不精确的日期并将其作为函数，您可以执行以下操作： p>

   - 功能定义
 CREATE FUNCTION SuperDuperDataCalculator（
 @BaseDate DATE ='20131016'，
 @EndDate DATE ='20131020'）
 RETURNS @Results TABLE（
 [Counter] INT，
 [Date] DATE）
 AS 
 BEGIN 
 WITH递归CTE AS（
 SELECT 
 1 AS [Counter]，
 @BaseDate AS [MyDate] 
 UNION ALL 
 SELECT 
 [Counter] + 1，
当时DATEADD（DAY，7，MyDate）> @EndDate THEN @EndDate ELSE DATEADD（DAY，7，MyDate）END 
 FROM 
递归CTE 
 WHERE 
 MyDate < @EndDate）
 INSERT INTO 
 @Results 
 SELECT * FROM RecursiveCTE OPTION（MAXRECURSION 0）; 
返回; 
 END; 
 GO 
 
 --Usage 
 SELECT * FROM SuperDuperDataCalculator（'20131016'，'20131020'）; 
 
  - 结果
计数器日期
 1 2013-10-16 
 2 2013-10-20 
  pre> 
 
 
请注意，我们必须使用多语句表值函数，因为在SQL Server中有一个错误，它不会让您在简单的方式中使用OPTION表值函数。另一种方法是从函数中删除OPTION（MAXRECURATION 0），并记住每次引用它（即相当糟糕的替代方法）使用它。
 
 
 
最后，如果你想返回最大的计数器值，你可以把它重写为一个标量值函数，即：
   - 函数定义
 CREATE FUNCTION SuperDuperDataCalculator（
 @BaseDate DATE ='20131016'，
 @EndDate DATE ='20131020'）
 RETURNS INT 
 AS 
 BEGIN 
 DECLARE @Results TABLE（
 [Counter] INT，
 [Date] DATE）; 
 DECLARE @ReturnValue INT; 
 WITH RecursiveCTE AS（
 SELECT 
 1 AS [Counter]，
 @BaseDate AS [MyDate] 
 UNION ALL 
 SELECT 
 [Counter ] + 1，
 CASE WHATE DATEADD（DAY，7，MyDate）> @EndDate THEN @EndDate ELSE DATEADD（DAY，7，MyDate）END 
 FROM 
递归CTE 
 WHERE 
 MyDate< @EndDate）
 INSERT INTO 
 @Results 
 SELECT * FROM RecursiveCTE OPTION（MAXRECURSION 0）; 
 SELECT @ReturnValue = MAX（[Counter]）FROM @Results; 
 RETURN @ReturnValue; 
 END; 
 GO 
 SELECT dbo.SuperDuperDataCalculator（'20131016'，'20131020'）; 
  
 
   1      28/11/2011 ...
How do I write a function in SQL to implement the above pattern?
解决方案
You could do something like this in SQL Server:
DECLARE @BaseDate DATE = '20111107';
DECLARE @EndDate DATE = GETDATE(); --Or the "end of dates in the database"
WITH RecursiveCTE AS (
    SELECT
        1 AS [Counter],
        @BaseDate AS [MyDate]
    UNION ALL
    SELECT
        [Counter] + 1,
        DATEADD(DAY, 7, MyDate)
    FROM
        RecursiveCTE
    WHERE
        MyDate < @EndDate)
SELECT * FROM RecursiveCTE OPTION (MAXRECURSION 0);
To handle dates that aren't exact and make this into a function you would do this:
--Function definition
CREATE FUNCTION SuperDuperDataCalculator (
    @BaseDate DATE = '20131016',
    @EndDate DATE = '20131020')
RETURNS @Results TABLE (
    [Counter] INT,
    [Date] DATE)
AS
BEGIN
    WITH RecursiveCTE AS (
        SELECT
            1 AS [Counter],
            @BaseDate AS [MyDate]
        UNION ALL
        SELECT
            [Counter] + 1,
            CASE WHEN DATEADD(DAY, 7, MyDate) > @EndDate THEN @EndDate ELSE DATEADD(DAY, 7, MyDate) END
        FROM
            RecursiveCTE
        WHERE
            MyDate < @EndDate)
    INSERT INTO
        @Results
    SELECT * FROM RecursiveCTE OPTION (MAXRECURSION 0);
    RETURN;
END;
GO

--Usage
SELECT * FROM SuperDuperDataCalculator('20131016', '20131020');

--Results
Counter Date
1   2013-10-16
2   2013-10-20
Note that we have to use a multi-statement table-valued function as there is a bug in SQL Server where it won't let you use OPTIONs in a simple table-valued function.  The alternative would be to remove the OPTION (MAXRECURSION 0) from the function and remember to use this every time you reference it (i.e. a pretty poor alternative).

...and finally, if you wanted to just return the maximum counter value you could rewrite this as a scalar-valued function, i.e.:
--Function definition
CREATE FUNCTION SuperDuperDataCalculator (
    @BaseDate DATE = '20131016',
    @EndDate DATE = '20131020')
RETURNS INT
AS
BEGIN
    DECLARE @Results TABLE (
    [Counter] INT,
    [Date] DATE);
    DECLARE @ReturnValue INT;
    WITH RecursiveCTE AS (
        SELECT
            1 AS [Counter],
            @BaseDate AS [MyDate]
        UNION ALL
        SELECT
            [Counter] + 1,
            CASE WHEN DATEADD(DAY, 7, MyDate) > @EndDate THEN @EndDate ELSE DATEADD(DAY, 7, MyDate) END
        FROM
            RecursiveCTE
        WHERE
            MyDate < @EndDate)
    INSERT INTO
        @Results
    SELECT * FROM RecursiveCTE OPTION (MAXRECURSION 0);
    SELECT @ReturnValue = MAX([Counter]) FROM @Results;
    RETURN @ReturnValue;
END;
GO
SELECT dbo.SuperDuperDataCalculator('20131016', '20131020');


                        
这篇关于计算功能中的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！