Calculation of date in function

   1      28/11/2011 ...

How do I write a function in SQL to implement the above pattern?

  • Insert and Relate Records in Single SQL Query?
  • Redgate SQL Prompt - how to reformat all files in source control repo
  • Needing sqlsrv driver for PHP on Mac
  • How do i import a mysql dump to SQL Server database
  • SQL Server and Hibernate - datetime2 vs varbinary
  • How to specify that a SQLCLR table valued function is ordered?
  • Insert into 2 tables with cursor, then use returned scope_identity to insert into another table
  • Rolling back and update command in Sql Server 2000
  • get row number of record in resultset sql server
  • How to use the DECLARE variables in WHERE CLAUSE
  • How to update a field's value based on a match condition for current data and one another?
  • “Invalid date format” when importing flat file with Import Wizard
  • 2 Solutions collect form web for “Calculation of date in function”

    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');
    

    Try this – It will get all the weeks and assign a rownumber in the subquery. Then only select the records where row number = 1 because there might be more results for that week. So hence RowNo = 1

     SELECT ROW_NUMBER() OVER(ORDER BY RowNo) AS IncrementalWeek,dte
     FROM 
     (
          SELECT DISTINCT DATEPART(ww,CONVERT(VARCHAR(20),createdDate,111)) AS [week],
                          CONVERT(VARCHAR(20),createdDate,111) AS dte,
                          ROW_NUMBER() OVER(PARTITION BY DATEPART(ww,Convert(VARCHAR(20),createdDate,111)) ORDER BY DATEPART(ww,CONVERT(VARCHAR(20),createdDate,111))) AS RowNo 
          FROM YourTable
    
     ) AS tble
    WHERE RowNo = 1
    ORDER BY [week]
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.