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)
[code language=”sql”]
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
[/code]
Looking for quality web hosting? Look no further than Arvixe Web Hosting!
