Table of days for 10 years - 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

SQL Server Development (2000)

Table of days for 10 years

Author

Topic

TimSinnott
Starting Member

48 Posts

Posted - 2002-10-03 : 14:33:26

Hi all,

I'm looking for a table containing a record for every day of the year for the next 10 years (about 3,650 records).

My reason is this: I'm building a report showing business occurrences (such as contracts signed) BY EACH DAY over a time interval, such as a month.

I want the report to show A LINE for every day in that time period, even if no contracts were signed that day, or even if the day is a holiday or weekend.

Has anybody heard of such a table? Any other ideas or thoughts?

Thanks in advance.

Tim

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-03 : 14:43:04

You can easily generate those dates using a sequence/tally table. This example is the most similar:

http://www.sqlteam.com/item.asp?ItemID=3332

And these have other examples of uses for sequence tables:

http://www.sqlteam.com/item.asp?ItemID=5857
http://www.sqlteam.com/item.asp?ItemID=2652

You don't need to stock the table with date values either, you can use the numbers in combination with DateAdd():

SELECT DateAdd(day, ID, '2000-01-01') AS [Date] FROM Tally WHERE ID<=3650