Post reply

Need to generate Datelist

  • March 31, 2006 at 1:00 pm

    #89909

    I need to generate a date listing by months + Year for the previous 13 months.

    like this

    03 - 2006

    02 - 2006

    01 - 2006

    12 - 2005

    11 - 2005  ETC...

    And it has to adjust itself to only the last thirteen months prior to the month I am currently in.

    I have tried a few DATEDIFF and DATEPART combinations but am not having any solid consistency

    any Help offered is appreciated.

     

    -R

    --Ron

  • March 31, 2006 at 1:55 pm

    #630231

    Never mind I got it.

    Here is what I did.

    Declare @counter1 INT

    Create Table TBL_DateRanges

    ( ID int IDENTITY(1,1),

      DATES varchar(11)  )

    Set @counter1 = -13

    while @counter1 <> 0

    begin

    INSERT INTO TBL_DateRanges (DATES)

    values (SUBSTRING(CONVERT( VARCHAR(6), DATEADD( m, @counter1, getdate()), 112),1,4) + ' ' +  SUBSTRING(CONVERT( VARCHAR(6), DATEADD( m, @counter1, getdate()), 112),5,6))

    set @counter1 = @counter1 + 1

    end

    go

    select * from TBL_DateRanges

    drop table TBL_DateRanges

    --Ron

Viewing 2 posts - 1 through 2 (of 2 total)

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