Archive

Archive for April, 2012

Create month end dates for reporting purpose

April 30, 2012 Leave a comment

Below is a simple SQL script to create 10 years worth of month ending dates data. Some time you may need such data created on the fly or to create a reporting period kind of table.

 Declare @VarYear int

Declare @VarMonth int

Set @VarYear = 2010

Set @VarMonth = 1

 While @VarYear < = 2020

Begin

      While @VarMonth < = 12

      Begin

      –Insert into dbo.ReportingPeriod

      –values(DATEADD(day,-1,convert(varchar,@VarMonth) + ‘/1/’ + @convert(varchar,@VarYear) ))

            Print ‘loop Value is :’+Convert (varchar, DATEADD(day,-1,convert(varchar,@VarMonth) + ‘/1/’ + convert(varchar,@VarYear) ))

            Set @VarMonth = @VarMonth+1

      End

Set @VarMonth= 1

Set @VarYear = @VarYear+1

END

Categories: SQL Server