Date Table

Question

Post reply

Date Table

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

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

Luke L SSC-Dedicated Points: 35547 More actions · Answer 1

The function in this link may provide you with the appropriate information...

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519&SearchTerms=F_TABLE_DATE

Else you may find the following useful..

http://www.intelligententerprise.com/db_area/archives/1999/992112/celko.jhtml

hope this helps.

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

Craig Sindorf Grasshopper Points: 14 More actions · Answer 2

Luke: I did find that one I just do not know what to change to have it create a table not a function.

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 3

Like any other function that returns a table, you should be able to use the function to create a permanent table...

[font=Courier New]

SELECT *

INTO dbo.newdatetable

FROM dbo.F_TABLE_DATE(somestartdate,someenddate)[/font]

My concern is... what do you think you're going to use a date table for? Except for the actual date column, if you're trying to resolve batches as opposed to single row GUI stuff, you may be in for a very rude awakening...

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

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

And, here's a good article by the gent that got me interested in Tally tables a thousand years ago... just remember, he doesn't really explain how to do the "business days between two dates" trick for batch code in a high performance manner... no one does for date tables (think about that)... 😉 but it does work for RBAR GUI stuff...

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

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