populating date table - 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

General SQL Server Forums

New to SQL Server Programming

populating date table

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2009-09-28 : 13:12:12

I have a table with two fields. Month and Year. I want to populate the table from 1995 through 2030 with the month field being 1 thru 12 for each year.

I know how I could populate the year, but its getting the month to repeat is my problem.

SET NOCOUNT ON
DECLARE @dt int
SET @dt = '1995'
WHILE @dt < '2030'
BEGIN
INSERT dbo.Calendar(Yeardt) SELECT @dt
SET @dt = @dt + 1
END

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-09-28 : 13:16:52

why, oh WHY would you break up a perfectly good data type like DATETIME into 2 different INT columns????

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Vack
Aged Yak Warrior

530 Posts

Posted - 2009-09-28 : 13:19:49

Because no one can seem to answer this:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=133545

I figure I'll just create a date table with month and year, and just link to that table.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-09-28 : 13:33:33

select 1995 + spt.number,a.number
from master..spt_values spt

CROSS JOIN
(select number from master..spt_values sp where number between 1 and 12 and type = 'P') a

where type = 'P'
and spt.number <= 2030 - 1995
order by 1,2
Jim

Everyday I learn something that somebody else already knew

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-09-28 : 14:14:03

This function can be used to populate a date table.

Date Table Function F_TABLE_DATE
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

CODO ERGO SUM

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-28 : 14:24:47

quote:
Originally posted by Vack

Because no one can seem to answer this:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=133545

I figure I'll just create a date table with month and year, and just link to that table.

The question (and problem resolution) in that other topic has nothing to do with your decision to store dates as [month] and [year] columns. DonAtWork's question is still a valid one in my book :)

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources