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!