Dates between two dates
-
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?
-
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 -
Well, I did that before with MySQL. I thought T-SQL has more functionality.
-
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
-
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
-
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...
-
Link doesn't work
-
try again. It's ok now
-
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 RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
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 RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Grant,
can you explain what is the xDate table ?
-
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 RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
If I have dateStart and dateEnd, how to calculate how many days between those two dates?
-
Use the datediff function.
select datediff(d,dateStart,dateEnd)
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
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
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)
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply