How to find all dates in a range
-
I want a SELECT statement giving all the dates in a range.
I.e.
I have two records (ID, DateFrom, DateTo), no time-part, dates in ISO-notation:
1 2005/10/28 2005/10/30
2 2005/10/15 2005/10/16
This is what I want as result from a SELECT statement
2005/10/30
2005/10/29
2005/10/28
2005/10/16
2005/10/15
Is this possible?
-
You want something like:
SELECT [DateFrom]
FROM MyTable
UNION
SELECT [DateTo]
FROM MyTable
You could add DISTINCT to the SELECT if you only wanted to return one of each date (if you had more than the 2 rows in your example).
-
Select t2.Date from Table2 t2
inner join Table1 t1 on t2.date between t1.DateFrom and t1.DateTo
GROUP BY t2.Date
ORDER BY t2.Date DESC
_____________
Code for TallyGenerator -
I think what Henk want is the select statement to return all dates between the DateFrom and DateTo.
Record 1 : 2005/10/28 to 2005/10/30
will return dates 2005/10/28, 2005/10/29, 2005/10/30.
Henk, Please correct if this not what you need.
I don't think a single select statement can return date (2005/10/29) not existed in a table.
The only solution i can think of is using while loop. Alternatively have a table pre-populated with all dates and join your table with that all dates table.
Any one else have better solution ?
-
KH, exactly what you said:
"I want a select statement to return all dates between the DateFrom and DateTo".
I think I have to make a table pre-populated with all dates (from now till the end of time) and join it with my DateFrom/DateTill - table.
Any one else have better solution ?
-
"The difficult can be done immediately... the impossible takes slightly longer."
This will work but the creation of a real "Numbers" table would be better as it would be more predictable and the performance would be much better...
SELECT DATEADD(dd,N,StartDate) AS TheDate
FROM yourtable y,
(--Derived table returns numbers from 0 to at least 255
--and takes the place of a short "numbers" table
SELECT (
SELECT COUNT(*)
FROM dbo.SYSOBJECTS son
WHERE son.ID < so.ID
) AS N
FROM dbo.SYSOBJECTS so
) Numbers
WHERE Numbers.N <= DATEADD(dd,DATEDIFF(dd,StartDate,EndDate),0)
ORDER BY TheDate DESC
The SYSOBJECTS table will always have at least 256 entries even on a band-spanking-new database... that would be you normal limit for how many days are between your start and end dates...
Like I said, it would be far better to create a real numbers table.
--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, as you say:
"The difficult can be done immediately... the impossible takes slightly longer."
Thank you for showing me the slightly longer solution
It's a beautiful solution, even comical.But, ... as I need only 6 weeks (42 days) your DATEADD approach with a real number table (0..41) will be satisfactory. Thanks a lot
-
Hi,
I had a similar task to return all values where I had a table with StartValue and EndValue fields
I created a cursor, here is a code idea. I had to deal with Integers, In your case you will have to use datediff and dateadd functions to get integer numbers per other posts above.
<all declarations first> then:
Declare curAD Cursor
For
Select StartValue, Endvalue From mytable
Order By StartValue
Open curAD
Fetch From curAD
Into @ValueStart,@ValueEnd
WHILE @@FETCH_STATUS = 0
BEGIN
SET @i = @ValueStart
WHILE @i < @ValueEnd + 1
BEGIN
Insert NewTable(Value)
Values (@i)
SET @i = @i + 1
END
Fetch Next From curAD
Into @ValueStart,@ValueEnd
END
Close curAD
Deallocate CurAD
Regards,Yelena Varsha
-
Yelena, thank you.
I never wordked with a cursor, I am more used to select statements, but it is nice to see this solution (now I see what KH ment, when he mentioned "using a while loop").
-
Henk,
Cursors are very close to Recordsets with a very similar syntax. I use cursors if performance is not a problem because their performance is not as good as Select statement. But sometimes it is easier to use a cursor logic: get the first row into the local variables, do the processing including getting values from the startvalue to the endvalue and inserting these values into another table or temp table. When completing processing of the first pair fetch next - load the second row into the same variables. Continue untill Fetch Status (@@Fetch_Status) is not null anymore which means end of recordset.
I use examples from Books Online and modify them for my tasks. Do not forget to Close and Deallocate the cursors to reclaim memory.
Regards,Yelena Varsha
-
You bet, Henk... thank you for the feed back.
I gotta know... what's comical about it?
--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) -
For reference:
Why should I consider using an auxiliary numbers table?
http://www.aspfaq.com/show.asp?id=2516
See the section on usage of the numbers stable to generate date ranges:
When you need to generate a set of dates in a range, the typical solution is to create a loop and iterate through the range, adding a day each time. However, a numbers table can help us generate this range as a set, instead of treating each date in the range individually. This way, you can use the code directly in a subquery or in a table-valued function, without having to worry about creating a temporary table to hold the values while you iterate through the loop.
DECLARE @sDate SMALLDATETIME, @eDate SMALLDATETIME
SET @sDate = '20040101'
SET @eDate = '20040229'
SELECT @sDate + Number
FROM dbo.Numbers
WHERE @sDate + Number <= @eDate
ORDER BY 1
SQL = Scarcely Qualifies as a Language
-
For reference:
Why should I consider using an auxiliary numbers table?
http://www.aspfaq.com/show.asp?id=2516
See the section on usage of the numbers stable to generate date ranges:
When you need to generate a set of dates in a range, the typical solution is to create a loop and iterate through the range, adding a day each time. However, a numbers table can help us generate this range as a set, instead of treating each date in the range individually. This way, you can use the code directly in a subquery or in a table-valued function, without having to worry about creating a temporary table to hold the values while you iterate through the loop.
DECLARE @sDate SMALLDATETIME, @eDate SMALLDATETIME
SET @sDate = '20040101'
SET @eDate = '20040229'
SELECT @sDate + Number
FROM dbo.Numbers
WHERE @sDate + Number <= @eDate
ORDER BY 1
SQL = Scarcely Qualifies as a Language
-
For reference:
Why should I consider using an auxiliary numbers table?
http://www.aspfaq.com/show.asp?id=2516
See the section on usage of the numbers stable to generate date ranges:
When you need to generate a set of dates in a range, the typical solution is to create a loop and iterate through the range, adding a day each time. However, a numbers table can help us generate this range as a set, instead of treating each date in the range individually. This way, you can use the code directly in a subquery or in a table-valued function, without having to worry about creating a temporary table to hold the values while you iterate through the loop.
DECLARE @sDate SMALLDATETIME, @eDate SMALLDATETIME
SET @sDate = '20040101'
SET @eDate = '20040229'
SELECT @sDate + Number
FROM dbo.Numbers
WHERE @sDate + Number <= @eDate
ORDER BY 1
SQL = Scarcely Qualifies as a Language
-
Yelena, thank you for the extended explanation.
But I used the code of Jef and Carl because it fits the best in the way I have to use these date. I always need (6 weeks = ) 42 days (from a Numbers table Nrs Field Nr 0..41) left outer joined with a table with start and end-dates. This From the first Monday before the start of the month (eg 2005-08-29 before the month september)
SELECT Dat, COALESCE(ISDATE(DatFrom), 0) AS Free
FROM (SELECT DATEADD(dd, Nr, '20050829') AS Dat FROM Nrs) AS D LEFT OUTER
JOIN DatPeriods DP ON D.Dat BETWEEN DatFrom AND DatTill
ORDER By Dat
It gives me 42 dates with Free 0 or 1
Ie:
Dat Free
2005-08-29 1
2005-08-30 1
2005-08-31 1
2005-09-01 1
2005-09-02 0
2005-09-03 0
2005-09-04 0
2005-09-05 1
2005-09-06 1
2005-09-07 1
etc.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply