Date Table populating in SQL 2000

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Date Table populating in SQL 2000

Author

Topic

avi25ge
Starting Member

3 Posts

Posted - 2005-11-09 : 04:39:02

Can you help me with a query to populate the Date Table with the following columns:

Date
Week
Weekday
Month
Quarter
Year
Day of the Year
Day of Week
Day of Month
Day of Quarter
Day of Year
Week of Year
Week of month

To populate from 2000 till 2010

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-09 : 04:46:12

Start with this

Declare @date table(d datetime)
Declare @d datetime

set @d='20000101'

While @d<='20101231'
Begin
	Insert into @date values (@d)
	set @d=@d+1
End
Select d from @date

Madhivanan

Failing to plan is Planning to fail

avi25ge
Starting Member

3 Posts

Posted - 2005-11-09 : 06:35:55

Thanks Madhivanan, would really appreciate if you can tell me how to populate this into a table that I have 'DDate' and also for Month, Quarter & Year incrementally

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-10 : 03:41:43

the rest of the columns that you required are not necessary in the table and can be calculated as per example below :


declare
	@in_date	datetime

select	@in_date 	= getdate()

select	_Date		= @in_date,
	_Week		= datepart(week, @in_date),
	_Weekday	= datepart(weekday, @in_date),
	_Month		= datepart(month, @in_date),
	_Quarter	= datepart(quarter, @in_date),
	_Year		= datepart(year,@in_date),
	_Day_Of_Week	= datepart(weekday, @in_date),
	_Day_Of_Month	= datepart(day, @in_date),
	_Day_Of_Quarter = datepart(dayofyear, @in_date) - datepart(dayofyear, dateadd(month, (datepart(quarter, @in_date) - 1) * 3, convert(char(4), year(@in_date)) + '0101')) + 1,
	_Day_Of_Year	= datepart(dayofyear, @in_date),
	_Week_Of_Month  = datepart(week, @in_date) - datepart(week, convert(char(6), @in_date, 112) + '01') + 1,
	_Week_Of_Year	= datepart(week, @in_date)

[KH]

avi25ge
Starting Member

3 Posts

Posted - 2005-11-10 : 03:59:54

Thanks Khatan, this helps..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-10 : 04:26:06

Well. In SQL Server help file, look for DateAdd, DateDiff, DatePart for more information

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources