CODEWITHSUNDEEP
phpsqlmysqldate
air
air

Reputation: 6264

calculate total number of days in specied dates

i have one table with two columns as shown in picture

alt text

table columns names are (MAXDATE,AMOUNT).

if you see we have 

   first date range (from current date to 20-jan-2010)
   second date range from 20-jan-2010 to 30-jan-2010 
   3rd range is from 20-jan-2010 to 31-jan-2010.

at the execution of page user enter the start and end date.

for example, if user put

      start date: 18-jan-2010
      end date: 23-jan-2010

then he has 2 dates in first options and 3 dates in second options.

what i want to calculate in sql

1. how many days in first range (if any this depends on supplied dates from user)
2. how many days in 2nd range  (if any this depends on supplied dates from user)
3. how many days in 3rd range  (if any this depends on supplied dates from user)

Thanks

Upvotes: 1

Views: 431

Answers (2)

Lèse majesté
Lèse majesté

Reputation: 8045

You can do all of this in MySQL:

SELECT DATEDIFF(NOW(), max_date) as days;
SELECT DATEDIFF(date2, date1) FROM
    (SELECT max_date as date1 FROM table1 LIMIT 1) as s1
    JOIN 
    (SELECT max_date as date2 FROM table1 LIMIT 1 OFFSET 1) as s2;
//etc.

Upvotes: 0

René Höhle
René Höhle

Reputation: 27295

Here is an example how to calculate days.

http://dev.mysql.com/doc/refman/5.0/en/date-calculations.html

Upvotes: 1

Related Questions