declare @date datetime
, @hour int
, @min int
, @fiscalyear int
set @date = '1/1/2006'
set @hour = 0
set @min = 0
while (@date <= '1/1/2020') begin
set @fiscalyear = (datepart(year,dateadd(month,(6),@date)))
insert into SearchDate (DateID
, Date
, DayOfWeekNumber
, DayOfWeekName
, WeekNumber
, DayOfMonthNumber
, MonthNumber
, MonthName
, QuarterNumber
, YearNumber
, FisalQuarterNumber
, FisalYearName)
values ((((10000)*datepart(year,@date)+(100)*datepart(month,@date))+datepart(day,@date))
, @date
, datepart(weekday,@date)
, datename(weekday,@date)
, (datepart(week,@date))
, (datepart(day,@date))
, (datepart(month,@date))
, datename(month, @date)
, (datepart(quarter, @date))
, (datepart (year, @date))
, (datepart(quarter,dateadd(quarter,(2),@date)))
, 'FY' + substring(CONVERT(nvarchar, @fiscalyear), 3, 2))
set @date = dateadd(day, 1, @date)
end