create a calendar tbl - SQL Server Forums

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)

create a calendar tbl

Author

Topic

JERICHO
Starting Member

18 Posts

Posted - 2002-10-09 : 02:04:40

hey guys may i noe is there any way that i can create a calender table with week no. year and it also take cares of the month and number of days within a month like example feb only got 28 days and etcs.

Andraax
Aged Yak Warrior

790 Posts

Posted - 2002-10-09 : 03:12:00

Hi Jericho!

Look into the DATEADD and DATEPART functions in BOL. They'll help you do what you want!

/Andraax

JERICHO
Starting Member

18 Posts

Posted - 2002-10-09 : 03:25:35

is there anyway i can sort of input the whole calendar into the database and elimating the number of days problem in the other words is there any function i can use to work with the system date from the computer

Andraax
Aged Yak Warrior

790 Posts

Posted - 2002-10-09 : 04:47:15

Try the code below. It will give you a table (calendar) filled with dates and stuff. You can alter it to suit your own needs. This is just an example.

SET NOCOUNT ON

create table calendar
(
date datetime primary key,
yearno int,
monthno int,
dayno int,
weekday varchar(20),
weekno int
)

declare @actdate datetime,
@fromdate datetime,
@todate datetime

--Choose fromdate and todate
select @fromdate='20000101',
@todate='20031231'

select @actdate=@fromdate

While @actdate<@todate
begin
insert calendar (date, yearno, monthno, dayno, weekday, weekno)
select @actdate,
datepart(yy, @actdate),
datepart(mm, @actdate),
datepart(dd, @actdate),
datename(dw, @actdate),
datepart(wk, @actdate)

select @actdate=dateadd(dd, 1, @actdate)
end

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-10-18 : 18:33:40

Just curious why create a calendar table when you have functions in SQL Server that will do the work for you.?

r937
Posting Yak Master

112 Posts

Posted - 2002-10-19 : 19:16:34

why create a calendar table? to flag the days the company offices are officially closed, to count the number of business days between two dates, to use in left outer joins for sales reports (including days when there were no sales)... the list goes on and on

rudy
http://rudy.ca/

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-10-20 : 08:19:05

you shouldn't need a calendar table to do this ... if you do need a specific date set ... so you can have all days and not have to store all days in your data table ... i.e. you need to do a cross join of sorts (i've done this with our budgeting data ... it only stores non-zero values and when i need a monthly or yearly report i cross join to a table like this [I use my Sequences table to create the calendar])


SET NOCOUNT ON

CREATE TABLE #Sequences (Value INT NOT NULL CHECK (Value >= 0) PRIMARY KEY NONCLUSTERED)

DECLARE @Value INT
SET @Value = 0

WHILE @Value < 1024
 BEGIN
	INSERT INTO #Sequences (Value) VALUES(@Value)
	SET @Value = @Value + 1
 END

DECLARE @BeginDate DATETIME, @EndDate DATETIME

SELECT @BeginDate = '01/01/2003'
SELECT @EndDate = '12/31/2003'

SELECT DATEADD(dd, Value, @BeginDate) AS Date
  FROM #Sequences
 WHERE DATEADD(dd, Value, @BeginDate) <= @EndDate
 ORDER BY DATEADD(dd, Value, @BeginDate)

Andraax
Aged Yak Warrior

790 Posts

Posted - 2002-10-20 : 16:52:36

I guess it depends on your situation. We use calendar tables quite often, as a central part of some of our calculations. If you use it often, it pays of pretty fast to have one ready instead of making one each time you need it.

r937
Posting Yak Master

112 Posts

Posted - 2002-10-20 : 20:31:22

onamuji, your comment "you shouldn't need a calendar table to do this" seems to contradict my comments about flagging business days

i am sure you meant that jericho does not need a calendar table for the original purposes, basic date functions like date differences, days in a month, etc.

however, a calendar table is definitely required if you need to flag business dates or similar facts that just aren't available in date functions

rudy

JERICHO
Starting Member

18 Posts

Posted - 2002-10-21 : 04:41:21

Hey thanks guys, i am somehow contricting too, due to continuing with someone else project, i notice he does the table so just curious on how to get it done but i dun see the need as i can use the systemdate within my PC to do it.

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources