Populate SQL Table with All Valid Dates Between Two Periods

I am working on a request from a client that required the use of a date table, but not just any date table. This table had some additional requirements;

1. They had to have the day number based on the start of the period. (This means if I started on April 1, 2014 then that would be Day 1)
2. Had to allow for beginning period other than January 1st. (This was because the fiscal year did not start on January)

After searching the internet I found many examples of each one of these requirements but nothing that fit all requirements so I wrote this small script. There are other options that would work just as well but this script allows for additional fields with additional attributes. For example you could actually add Holidays, or even eliminate any weekends from the ‘Day Count’ or add both.

There is really only two parameters and they are;
* @Startmonth (This is the month to start the period)
* @StartYear (What Year do you want to start this)

declare @EndDate datetime
Declare @startmonth int
Declare @endMonth int
Declare @startyear int
Declare @year int
Declare @CurrentDay int
Declare @sCurrentDay int
Declare @NumofMonth int
Declare @DateisValid bit
Declare @CalcMonth int
Declare @convertedDate smalldatetime

Set @Startmonth = 4
Set @startYear=2013

Set @NumofMonth = 12 + @startmonth
Set @year= @startYear

declare @temp  table
(
DayId int identity(1,1),
Month int,
Day int,
Year int,
WorkDate datetime
)

While (@Startmonth<@NumofMonth)
Begin
  --Determine the Year based on the Month Starting.  If Month is greater than 12 then
  --it must be the next year
  Set @year = case when @startmonth>12 then @StartYear +1
              else @StartYear
              end

   set @CalcMonth = @Startmonth   --Sets the Calculated Month equal to the StartMonth

   --If the Month is greater than 12 then it must be in the next calendar year so subtract 12
    If @startmonth>12
      Set @CalcMonth = @Startmonth-12

   --Start the cycling of the days
   Set @CurrentDay = 1

   --Loop 31 times for every month
   While @CurrentDay < 32
     Begin
     --Resets the Valid Date Flag
     Set @DateisValid=isdate(cast(@CalcMonth as varchar(2)) + '/' + cast(@currentDay as varchar(2)) + '/' + cast(@Year as varchar(4)))

      --Resets the Valid COnverted Date
      Set @converteddate = null
      IF @DateisValid = 1
      Begin
        Set @convertedDate = cast(cast(@CalcMonth as varchar(2)) + '/' + cast(@currentDay as varchar(2)) + '/' + cast(@Year as varchar(4)) as datetime)
           insert into @temp
      values (@Calcmonth, @CurrentDay,@year, @convertedDate)

      End
      Set @CurrentDay = @CurrentDay + 1
   End
    Set @Startmonth = @Startmonth + 1
End

Select * from @temp

Delete @temp

Looking for quality web hosting? Look no further than Arvixe Web Hosting!

Tags: , , , , , , , , , , , , , | Posted under Programming/Coding | RSS 2.0

Author Spotlight

David Bauernschmidt

David Bauernschmidt

I live in the historical triangle of Virginia where I am married with two daughters. I have spent over 13 years working for a Fortune 500 company in the computer area. I started in VB 6.0 and by the time I ended my employment I was supervising a development team where we built many web applications. When my first daughter was born I wanted to spend more time with her so I left and became a programmer analyst for local government as well as launch my own company. Since then I have grown James River Webs into a profitable web design and application company helping small businesses create a big presence on the internet. As an employee I have created web application used by citizens and other companies. I enjoy fly fishing, and spending time with my family. I also enjoy learning new approaches and development tools when it comes to developing applications.

Leave a Reply

Your email address will not be published. Required fields are marked *


2 × = 6

You may use these HTML tags and attributes: <a href="" title="" rel=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>