Home > SQL Server > Create month end dates for reporting purpose

Create month end dates for reporting purpose

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

Advertisements
Categories: SQL Server
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: