Post reply

SELECT Question

  • January 4, 2007 at 2:35 pm

    #681340

    You can use ROW_NUMBER() in SQL Server 2005. This is similar to 'rownum' in Oracle. A list of dates can then be obtained as follows:

    --------------------------------------------------------------

    -- Code to return a sequence of dates in SQL Server 2005

    --

    -- This works by querying a table with many rows in it,

    -- and using the ROW_NUMBER() function to get the row number.

    -- You will need to enter the name of a table with sufficient

    -- rows for your version to work.

    -- ie:

    -- Replace 'MyTable' with name of a large table

    -- Replace 'MyColumn' with column name in table.

    --------------------------------------------------------------

    BEGIN

    DECLARE @StartDate DATETIME

    DECLARE @NumberOfDays BIGINT

    SET @StartDate = '01-JAN-2006'

    SET @NumberOfDays = 100

    SELECT @StartDate + (Num-1) AS DateSequence

    FROM

    (

    SELECT ROW_NUMBER() OVER (ORDER BY MyColumn) AS 'Num'

    FROM (

    SELECT TOP (@NumberOfDays) MyColumn

    FROM MyTable

    ) BIG_TABLE

    ) NUMBER_TABLE

    END

     

Viewing post 16 (of 15 total)

You must be logged in to reply to this topic. Login to reply