CODEWITHSUNDEEP
sqlsql-serversyntaxreporting-serviceswhere-clause
JsonStatham
JsonStatham

Reputation: 10364

SQL WHERE query on date range

In my table I have 118 records detailing projects. The 2 fields I am concerned with here are startdate and enddate.

I need to produce a report from this view which shows which projects were 'active' between the following date ranges:

01/01/2011 - 01/12/2011

I have tried the following WHERE clase:

WHERE startdate BETWEEN '01/04/2011' AND '01/12/2011' 
OR enddate BETWEEN '01/04/2011' AND '01/12/2011'
OR startdate <= '01/04/2011' AND enddate >= '01/12/2011'

What comes through does not seem correct, there are only a few records displayed and many which I know for a fact should be displayed are not, such as one project with a start date of 20/07/2011 and enddate of 21/11/2011 dissapears when the WHERE query is run.

Can anyone see a fault with this WHERE query

enter image description here

Upvotes: 3

Views: 743

Answers (5)

JsonStatham
JsonStatham

Reputation: 10364

My original query was working, the database I was connecting to however had different date formats to my query.

Upvotes: 0

benni_mac_b
benni_mac_b

Reputation: 8877

Before the query add

set dateformat dmy

Also maybe add some brackets

WHERE 
(startdate BETWEEN '01/01/2011' AND '01/12/2011')
OR 
(enddate BETWEEN '01/01/2011' AND '01/12/2011')
OR 
(startdate <= '01/01/2011' AND enddate >= '01/12/2011')

Upvotes: 1

Ben
Ben

Reputation: 52863

Everyone's looking at this the wrong way round comparing startdate and enddate to a string when you can compare the string to the columns; the following is the simplest way of ascertaining what you want:

where ( '01/04/2011' between startdate and enddate
          or  '01/12/2011' between startdate and enddate
         )

Upvotes: 0

MatBailie
MatBailie

Reputation: 86706

WHERE
    startdate <= '2011-12-01'
AND enddate   >= '2011-01-01'

(Assuming the value in enddate is the last date the project is active)

Examples using numbers, searching for anything that overlaps 100 to 200...

Start | End | Start <= 200 | End >= 100

 000  | 099 |  Yes         | No
 101  | 199 |  Yes         | Yes     (HIT!)
 201  | 299 |  No          | Yes
 000  | 150 |  Yes         | Yes     (HIT!)
 150  | 300 |  Yes         | Yes     (HIT!)
 000  | 300 |  Yes         | Yes     (HIT!)

This absolutely needs an AND in the logic :)


In terms of your query...

Your query with parenthesis, looks like this...

WHERE
  (
     startdate BETWEEN '01/04/2011' AND '01/12/2011'
  OR enddate   BETWEEN '01/04/2011' AND '01/12/2011'
  OR startdate <= '01/04/2011'
  )
  AND enddate >= '01/12/2011'

But your example never meets the last AND condition. Try adding parenthesis to be more explicit...

WHERE
     (startdate BETWEEN '01/04/2011' AND '01/12/2011')
  OR (enddate   BETWEEN '01/04/2011' AND '01/12/2011')
  OR (startdate <= '01/04/2011' AND enddate >= '01/12/2011')

Upvotes: 5

Stuart Blackler
Stuart Blackler

Reputation: 3772

Assuming startdate and enddate are date fields,

Try this:

WITH Dates AS ( 
      SELECT [Date] = @StartDate
      UNION ALL SELECT [Date] = DATEADD(DAY, 1, [Date])
      FROM Dates WHERE [Date] <= DATEADD(DAY, -1, @EndDate)
) 
-- YOUR SELECT STATEMENT
-- YOUR FROM STATEMENT
CROSS APPLY Dates
WHERE [Date] BETWEEN startdate AND enddate 
-- The rest of your where statement here
OPTION(MAXRECURSION 0);

Declaring your start date as 01/01/2011 and your end date as 01/12/2011

Upvotes: 0

Related Questions