Populating a table
-
Hi,
I have a table with the following columns
id, date,day_of_month,month_of_year,week_of_year
I need to populate this table with dates from 1st Jan 2003 to 31Dec 2003.
How can i do this using a proceedure? help anyone
So for example i should fill up the table like below
ID date day_of_month month_of_year week_of_year
1 1-jan-2003 1 1 1
2 2-jan-2003 2 1 1
|| || || || ||
32 1-feb-2003 1 2 5
|| || || || ||
365 31-dec-2003 31 12 52
-
create table dates
(
id smallint identity (1, 1),
"date" datetime,
day_of_month tinyint,
month_of_year tinyint,
week_of_year tinyint
)
go
declare @d datetime
set @d = '2003-01-01'
while (@d < '2004-01-01')
begin
insert dates ("date", day_of_month, month_of_year, week_of_year)
select @d,
datepart(dd, @d),
datepart(mm, @d),
datepart(wk, @d)
set @d = dateadd(dd, 1, @d)
end
select * from dates
-
Thankyou very much
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply