2007-09-13

SQL Server Day Vector

Here is SQL server code to generate a day vector:

create table day_vector (
  date_index bigint primary key,
  full_date datetime unique,
  year_part int,
  month_part int,
  day_part int
)

go
declare @first_date datetime, @last_date datetime
set @first_date = '2000-01-01'
set @last_date = '2050-12-31'

declare @date datetime
set @date = @first_date

while @date <= @last_date begin
  declare @date_index int
  set @date_index = datediff(day, @first_date, @date)
  insert into day_vector
           (date_index,  full_date, year_part,             month_part,             day_part)
    values (@date_index, @date,     datepart(year, @date), datepart(month, @date), datepart(day, @date));
  set @date = dateadd(day, 1, @date)
end

go
at