Get list of dates between 2 dates

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Get list of dates between 2 dates

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2009-03-19 : 09:16:31

Hi,
I have a table with a datetime column(date reported)
.I need to return information from the table between 2 dates.
For example between 01/01/2009 and 08/01/2009
However, if for example there is no date 03/01/2009 and 07/01/2009 (UK) in the table then i want to the result to appear as follows
01/01/2009 2
02/01/2009 3
03/01/2009 0
04/01/2009 3
05/01/2009 3
06/01/2009 3
07/01/2009 0
08/01/2009 4

How can i do that?

Thnks

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-19 : 09:57:35

You can use a Numbers or Tally table (or a function that generates numbers) to create a row for each date then LEFT OUTER JOIN to your table. Like this:


declare @stdt datetime
       ,@enddt datetime

select @stdt = '2009-01-01'
       ,@enddt = '2009-07-01'

select dateadd(day, nu.number, @stDt)
       ,count(o.id)
from   master..spt_values nu --Any Table of consecutive integers
left outer join sysobjects o --YourTable
       on o.crdate = dateadd(day, nu.number, @stDt)
where  nu.type = 'p'
and    nu.number <= datediff(day, @stDt, @endDt)
group by dateadd(day, nu.number, @stDt)

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-19 : 11:53:56

or create one yourselves and use it like


DECLARE @StartDate datetime,@EndDate datetime

SELECT @StartDate =MIN(datecol),@EndDate=MAX(datecol)
FROM YourTable

;With Calendar_CTE (DateVal)
AS
(
SELECT @StartDate
UNION ALL
SELECT DATEADD(dd,1,DateVal)
FROM Calendar_CTE
WHERE DATEADD(dd,1,DateVal)<=@EndDate
)
SELECT  c.DateVal,COALESCE(Countfield,0)
FROM Calendar_CTE c
LEFT JOIN (SELECT datecol,countfield
           FROM YourTable
           GROUP BY datecol)t
ON t.datecol=c.DateVal
OPTION (MAXRECURSION 0)

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2009-03-20 : 12:33:11

Thanks both for the help :-)

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-20 : 12:39:46

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources