April 5, 2007 at 8:32 am
Is there a function in SQL to get all dates between two dates? Or I have to add one day to the start date till the date is not more than the end date?
April 5, 2007 at 8:43 am
There is not a function to do it. A loop that adds one day at a time to the start date is an easy way to do it.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
April 5, 2007 at 8:48 am
Well, I did that before with MySQL. I thought T-SQL has more functionality.
April 5, 2007 at 9:50 am
He he , Your funny, unfortunatelly t-sql and dates is not the functionality that sets MSSQL apart from Mysql.
Its always a good idea to have a table of dates, or a table of numbers to help you with such a task.
SELECT TOP 500 Number = IDENTITY(int, 1, 1)
INTO #Numbers
FROM sysobjects t1, sysobjects t2, sysobjects t3
select dateadd(dd,Number,'01/01/2007')
from #Numbers
where dateadd(dd,Number,'01/01/2007') <= '6/24/2007'
drop table #Numbers
April 5, 2007 at 9:55 am
If you don't have a table of numbers or a calendar, make use of this excellent function from MVJ
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519&SearchTerms=F_TABLE_DATE
April 5, 2007 at 10:01 am
Well, I don't think it's a good idea to have a table of dates. You mean to create it on the fly and then drop it? What if something happens and it doesn't get dropped. I will end up with some garbage. Then I have to write and schedule a script to clean garbage / temp tables from the database...
April 5, 2007 at 10:03 am
Link doesn't work
April 5, 2007 at 10:12 am
try again. It's ok now
April 5, 2007 at 10:40 am
No promises on performance but:
DECLARE
@BeginDt DATETIME;
DECLARE @EndDt DATETIME;
SET
@BeginDt = '1/1/2007';
SET @EndDt = '2/10/2007';
WITH
xDate AS
(SELECT @BeginDt AS d1
UNION ALL
SELECT DATEADD(DAY,1,d1) AS d2
FROM xDate
WHERE d1 <= @EndDt)
SELECT
d1 FROM xdate
WHERE d1 BETWEEN @BeginDt AND @EndDt
Seems to work.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 5, 2007 at 10:46 am
Oops. Did a bit more testing & found you need to add this at the end:
OPTION(MAXRECURSION 0);
Or you could add a datediff to get the number of days difference between the start & stop and use that to set the maxrecursion to a reasonable number. Either way, it seems to spit the stuff right out.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 5, 2007 at 11:04 am
Grant,
can you explain what is the xDate table ?
April 5, 2007 at 11:15 am
Sure, it's a common table expression. It's new in 2005. They function similar to derived tables, but can be referenced multiple times in a give procedure and, most importantly for this example, can be called recursively.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 5, 2007 at 1:35 pm
If I have dateStart and dateEnd, how to calculate how many days between those two dates?
April 5, 2007 at 1:40 pm
Use the datediff function.
select datediff(d,dateStart,dateEnd)
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
June 23, 2007 at 9:50 pm
A bit late with a response... but you do understand that temp tables are automatically dropped when the session that created it ends?
--Jeff Moden
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply