SQL Server Performance Forum – Threads Archive

Insert first date of each quarter between 2 dates?

I have a temp table used by a stored procedure that contains some dates and values. I have a begin date and end date for a period of time and I need to insert the first day of each calendar quarter that is included in my date range into my temp table. For example: If the date range is ‘1/1/2000’ – ‘6/15/2001’ I would need to insert 1/1/2000, 4/1/2000, 7/1/2000, 10/1/2000, 1/1/2001, 4/1/2001. Is there a quick way to do this in SQL code? (I should mention this procedure runs thousands of times per day so it must execute fast)

DECLARE @start DATETIME, @end DATETIME
SELECT @start = ‘20000101’, @end = ‘20010615’
SELECT DATEADD(quarter, DATEDIFF(quarter,0, @start)+Number,0 )
FROM master..spt_Values
WHERE Type=’P’
AND DATEADD(quarter, DATEDIFF(quarter,0, @start)+Number,0 ) < @end
—
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

If it runs 1000’s of times a day, consider the use of a calendar table. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>

Thats a cool solution Frank. I’ve never dipped into that spt_Values table before. I assume those values and that table are there for other reasons. Can you tell me what else that table is used for?

It is an undocumented lookup table

Yes, it’s an internal helper table several of the system procedures use. It’s a nice little toy for quick and dirty solutions and sample scripts. In your case I would think about a number table and use that instead of spt_Values. You knwo, as with all undocumented stuff in SQL Server it might change or disappear at Microsoft’s will. When you look at spt_Values, you’ll see that this is kind of a superlook-up table. It mixes numbers with numerics in the same domain and might be viewed as a nightmare structure. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Btw, if you can enforce that your start date is always also the first day of a quarter, you don’t need this DATEDIFF stuff to calibrate to the first day and thus can simplify your query to <pre id="code"> SELECT DATEADD(QUARTER, Number, @start ) FROM master..spt_Values WHERE Type=’P' AND DATEADD(QUARTER, Number, @start ) < @end ORDER BY Number </pre id="code"> I’ve included now an ORDER BY and would also suggest it as only this make absolutely sure that you start with the lowest number. – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>

]]>