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