mysql_query日期型,MySql Query - 日期范围内的日期范围

I use mySql 5 and IIS.

I have products, that have a start date field and an end date field.

I need to run a query that will take user entered Start and End dates, and output the number of days that the product ran within the date range.

Example:

Offer1 - July 1 2011 thru July 31 2011

Query - July 1 2011 thru Sept 15 2011

Results = 31

Example:

Offer1 - July 1 2011 thru July 31 2011

Query - July 1 2011 thru July 15 2011

Results = 15

解决方案

If your products have a start_date and an end_date and your query has a qstart_date and a qend_date, then we want the number of days between:

GREATEST(start_date, qstart_date)

and

LEAST(end_date,qend_date)

. In MySQL I think this looks like

1 + DATEDIFF ( 'd' , GREATEST(start_date, qstart_date) , LEAST(end_date,qend_date) )

And you'll want to ignore negative numbers, replacing them with "0".