list of dates
-
Hello,
Given a start date and end date, is it possible to construct a select statement whose result is a list of dates spanning the timeframe specified? That is, given '01-Sep-2004' and '30-Jun-2005', can I select a list like: '01-Sep-2004', '02-Sep-2004', '03-Sep-2004',... '30-Jun-2005'?
thanks
--
Shane
-
Create a calendar table. TheDate Smalldatetime clustered index primary key and fill it untill 20??
Then just do select from Calendar where Date between ? and ?
-
I am 'assuming' you need a recordset returned. [Again, the BullDozer (Bully?) raises its ugly head]. Here is a possible, though not necessarily elegant or efficient solution:
DECLARE @BeginDate varchar(25),
@EndDate varchar(25),
@OutPut datetime
SET @BeginDate = '01-Sep-2004'
SET @EndDate = '30-Jun-2005'
SET @OutPut = @BeginDate
CREATE TABLE #DatesSelected( [Date] varchar(25))
WHILE @Output <= @EndDate
BEGIN
INSERT INTO #DatesSelected( [Date]) SELECT CONVERT( varchar, @Output, 101)
SELECT @Output = DATEADD( day, 1, @Output)
END
SELECT * FROM #DatesSelected
DROP TABLE #DatesSelected
(far more reusable is you use Remi's solution.... didn't see that until after I posted... )
I wasn't born stupid - I had to study.
-
Remis Calendar solution is the correct way!
But just for the fun of it you can always use the numbers( or sequence, like others call it) table
select Dateadd(d,n,@startdate) Dte
from numbers
where n between 0 and datediff(d,@startdate,@enddate)
* Noel -
A DB shouldn't be without a calendar and a numbers table
. -
Ya... that should save you a KBs on the HD
. But might be costlier on the CPUS. Always that depends thing.........
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply