+ Reply to Thread
Results 1 to 2 of 2

Thread: SQL : Date Range

  1. #1

    SQL : Date Range

    I have tables named:
    Code:
    Posts_August_2011
    Posts_September_2011
    Posts_October_2011
    Posts_November_2011
    Posts_December_2011
    Posts_January_2012
    I want to create stored procedure that would UNION tables,but that is not the problem since I can use dynamic SQL to glue it up, my problem is getting Months and Year Range from RANGE to some kind of list? and then checking if table exist, Union them and return.

    I would declare 2 variables @FromDate and @Todate as datetime

    What would be the best way to extract month name and year for each of the months in given range.

    Say I give it a range @FromDate = '2010-08-13 07:00:28.000' and @ToDate = '2011-12-30 23:59:00.000' can someone tell me how to extract Month and year.

    using dinamyc SQL is acceptable,It could check for existence of table in while loop and glue to string for each table that exists in range as:
    Code:
    @SqlToExecute += 'SELECT * FROM Posts_' + extractedMonthName + '_' + extractedYear + ' UNION ALL ';

  2. #2
    joey
    Guest
    To get names of tables Posts_% and date extracted from their names you can use following code:
    Code:
    create table Posts_August_2011(i int)
    GO
    create table Posts_September_2011(i int)
    GO
    
    DECLARE @SqlToExecute varchar(max)
    DECLARE @FromDate date = '20110801', @ToDate date = '20110809'
    
    SELECT  name PostName, CAST(REPLACE(REPLACE(name,'Posts_','01/'),'_','/') as date) PostDate
    INTO #Posts
    FROM sys.tables
    WHERE name like 'Posts_%'
    
    SET @SqlToExecute=''
    
    SELECT @SqlToExecute += 'SELECT * FROM ' + PostName + ' UNION ALL '
    FROM #Posts
    WHERE PostDate>=@FromDate
          AND PostDate<@ToDate
    
    SELECT SUBSTRING(@SqlToExecute,1, LEN(@SqlToExecute)-10)
    
    DROP TABLE #Posts
    DROP TABLE Posts_August_2011
    DROP TABLE Posts_September_2011

+ Reply to Thread

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts