Enhanced DateDiff

1. Junior Member
Join Date
Dec 1969
Posts
17

## Enhanced DateDiff

Hi,<BR>How can I display all the days, months (and even years) in selected period of time (startdate, enddate)?<BR>DateDiff ("d",startdate,enddate) returns number of days in period, and I&#039;ll use it for loop, but I need also days and months,i.e.:<BR>for i = 1 to DateDiff("d",startdate,enddate)<BR>MySQL="SELECT sum(Field1) from Table where Datefield between mmddyyyy and mmddyyyy+1"<BR>Next<BR>&#039;mmddyyyy represents each day in selected period<BR>Thanx in advance,<BR>MrM

2. Senior Member
Join Date
Dec 1969
Posts
273

## RE: Enhanced DateDiff

I don&#039;t fully understand what you want. Is it a cumulative total or the sum for each day?<BR><BR>ie. with the following data what result would you require?<BR><BR>2003-08-10 100<BR>2003-08-11 150<BR>2003-08-12 200<BR><BR>Either way I would start with the following SQL and do the rest of the calculation in the ASP:<BR><BR>SELECT convert(char(10), Datefield , 120), sum(Field1)<BR>from Table<BR>group by convert(char(10), Datefield , 120)<BR>order by convert(char(10), Datefield , 120)<BR><BR>Let me know if you need more help, or if I have just misunderstood the problem.<BR><BR>Gavin

3. Junior Member
Join Date
Dec 1969
Posts
17

## RE: Enhanced DateDiff

Hi Gavin,<BR>U misunderstood me - my fault, apparently I wasn&#039;t clear enough...<BR>What I actually want is to list all the days, months and even year in selected period of time.<BR>Like: in period 27/02/2003 - 02/03/2003 it would be<BR>27/02/2003<BR>28/02/2003<BR>01/03/2003<BR>02/03/2003<BR><BR>Leap years should be taken in mind also.<BR>MrM

4. Senior Member
Join Date
Dec 1969
Posts
7,686

## RE: Enhanced DateDiff

one possible solution would be to create a so-called tally table. This is a table with only one column &#039;N&#039;, like this:<BR><BR>CREATE TABLE Numbers (N int)<BR><BR>now insert the numbers 0 till (say) 10000 in this table, and you can use the table like this:<BR><BR>SELECT DateAdd(d,N.n,[startdate]) FROM<BR>Numbers n<BR>WHERE n &#060; DATEDIFF(d,[startdate],[enddate]<BR><BR><BR>the syntax for DATEDIFF and DATEADD may vary depending on your RDBMS<BR><BR><BR><BR>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•