Dates of the month

Question

Post reply

Dates of the month

Doug-363866

Say Hey Kid

Points: 674
More actions
September 4, 2009 at 12:22 pm

#134905

How would I insert the dates of the current month 2009-09-01, 2009-09-02, etc, etc, into a sql table using only sql?

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

If you have a Numbers table (Tally table, Sequence table), it's easy:

select dateadd(day, number-1, '9/1/2009')

from dbo.Numbers

where number <= datediff(day, '9/1/2009', dateadd(month, 1, '9/1/2009'));

If you don't have a Numbers table, and don't want to create one (they're tremendously useful for a lot of things), you can use a CTE to generate one, like this:

;with Numbers (Number) as

(select row_number() over (order by object_id)

from sys.all_objects)

select dateadd(day, number-1, '9/1/2009')

from Numbers

where number <= datediff(day, '9/1/2009', dateadd(month, 1, '9/1/2009'));

You can, of course, use variables for the month you want, or columns, or any datetime calculation.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

If you want to know how a Tally table (or CTE that works like one) replaces a Cursor or While Loop in many cases, please see the following article...

http://www.sqlservercentral.com/articles/T-SQL/62867/

--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)

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

Just to keep this clear for the newcomers. A "numbers" table and a "tally" table are the same thing.

But "Tally Table" is much more alliterative and fun. 😉

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Doug-363866 Say Hey Kid Points: 674 More actions · Answer 4

Is a numbers//tally//sequence table the same as a PIVOT table?

Thank you for the responses.

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 5

Nope, it's just a table containing sequential numbers (1, 2, 3, 4, 5, ...) but there are many solutions which can be done with. You should read the article posted by Jeff. You can also find a link in my signature.