quick way to create a table that has every day of the year in it?

Question

quick way to create a table that has every day of the year in it?

Snargables

SSCrazy Eights

Points: 8855
More actions
December 16, 2011 at 9:59 am

#253664

Anyone know how to do this. I've done it in the past however forgot which sys table i used for generating this.
create table #date
(
dayOfYear datetime
)
insert into #date ( dayOfYear )
select???????????

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

here's a copy of mine named TallyCalendar, which generates dates from the SQL beginning of time (1900-0101) to 100 years in the future compared to today.

the advantage is i've added a lot of things based on experience, like Daylight Savings time, ISOweeks, integers for Day/Month/Year, Julian dates, and more.

i'm also generating alot of other columns, and include stuff like IsWeekday,IsHoliday, etc, which can be helpful for calculating business days.

see if this is helpful for you:

TallyCalendar_Complete_With_DST.txt

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

Wow.

Even the requirement of knowing whether or not the moon is gibbous is catered for 🙂

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Lowell SSC Guru Points: 323518 More actions · Answer 3

Phil Parkin (12/16/2011)
Wow.
Even the requirement of knowing whether or not the moon is gibbous is catered for 🙂

Adding the moon thing came about with the question "when is the next Friday the thirteenth , or the next Halloween that is also a full moon."

well, now that it's in the table, it's trivial to get.

I've actually been playing with putting the surise/sunset in there too, so you just pass latitude and longitude, but i can't quite get it to work yet.

Once I know I've got that working right, i'll add that too.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

captcooldaddy (12/16/2011)
Anyone know how to do this. I've done it in the past however forgot which sys table i used for generating this.
create table #date
(
dayOfYear datetime
)
insert into #date ( dayOfYear )
select???????????

To answer the question without a Calendar table...

DECLARE @DesiredYear CHAR(4);

SELECT @DesiredYear = '2011'

SELECT DayOfYear = DATEADD(dd,Number,@DesiredYear)

INTO #Date

FROM master.dbo.spt_Values

WHERE Type = 'P'

AND Number >= 0

AND Number < DATEDIFF(dd,@DesiredYear,DATEADD(yy,1,@DesiredYear))

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ron McCullough SSC Guru Points: 63877 More actions · Answer 5

Lowell (12/16/2011)
here's a copy of mine named TallyCalendar, which generates dates from the SQL beginning of time (1900-0101) to 100 years in the future compared to today.
the advantage is i've added a lot of things based on experience, like Daylight Savings time, ISOweeks, integers for Day/Month/Year, Julian dates, and more.
i'm also generating alot of other columns, and include stuff like IsWeekday,IsHoliday, etc, which can be helpful for calculating business days.
see if this is helpful for you:
TallyCalendar_Complete_With_DST.txt

Lowell -Fantastic amount of work, Thanks for sharing with all

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]