MySql Query- Date Range within a Date Range

Question

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

Chris Cunningham · Accepted Answer · 2011-07-05 22:16:00Z

4

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".

edited Jul 5, 2011 at 22:16

answered Jul 5, 2011 at 21:41

Chris Cunningham

1,8861 gold badge15 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

OMG Ponies Over a year ago

+1: Could also wrap the DATEDIFF in ABS if the start & end get reversed.

ypercubeᵀᴹ Over a year ago

@Chris: You also need to add +1 to this result. As it is, you'll get 31-1 = 30

Chris Cunningham Over a year ago

@OMG Ponies We can't use ABS because then we will mess up sometimes and think that there were some overlaps. Negative numbers here mean "nope, the date ranges didn't overlap; they missed by this much."

Collectives™ on Stack Overflow

MySql Query- Date Range within a Date Range

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related