Calculation of date in function

   1      28/11/2011 ...

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

  • Get places in radius of a certain point using SQL geography
  • Preserving ORDER BY in SELECT INTO
  • Start SQL Server Jobs when field = specific value
  • SQL Server restore and backup per schema
  • How can I use single query to insert multiple records from Dataset into SQL Server 2005?
  • How can I find out what FOREIGN KEY constraint references a table in SQL Server?
  • T-SQL CASE statement relies on another CASE statement in same SELECT query
  • How Do I Use PIVOT On This Data:?
  • How to do the equivalent of a 'Tsql select into', into an existing table
  • Optimizing Delete on SQL Server
  • How to get node name and values from an xml variable in t-sql
  • How can I conditionally construct a table name for an SQL CREATE TABLE statement?
  • 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.