Multiple Dates Ranges in Sql Select Query

Question

I am currently working on C# with Sql Server Language, trying to design a query where I have to select quarters(Q1, Q2, Q3, Q4).

My issue is that, suppose if the user from the front end selects only Q1 then I can set the query to date range for quarter Q1, and if the user selects Q1 and as well Q2 then I can still set the date range for Q1 and Q2 because they fall under sequence date ranges.

But if the user selects Q1 and Q3 then how do I prepare date range query(either in stored procedure or simple select query) and excecute for this.

I was trying with this but no use.

where CDate in(cdate between '2015-05-20' and '2015-06-01' 
  and CDate between '2016-06-03' and '2016-06-04'

select * from MM  where @innerstring

The @innerstring I want to send as a parameter to the sql stored procedure and then prepare/execute it. but I don't want to use temp table to store query data for saperate date ranges and then union that result. because I have different parameters with me as well.

Thanks.

Solution

You don't need the in. This should work:

where (cdate between '2015-05-20' and '2015-06-01' or
       cdate between '2016-06-03' and '2016-06-04'
      )

Note that between with dates can be dangerous/midleading, as Aaron Bertrand explains in an interesting blog. I would go for:

where (cdate >= '2015-05-20' and cdate < '2015-06-02' or
       cdate >= '2016-06-03' and cdate < '2016-06-05'
      )

This safely works even when cdate has a time component.

Comments

[jarlh]

Why the MySQL tag?

[Deadsheep39]

You have to add example data and expected result, we do not fully understand what you would like to do with quaters or dates.

[Saleem]

Nop its just selecting the records for the first date range after or its doing nothing. I want to include the result for both the date ranges, I mean select all records from cdate >= '2015-05-20' and cdate < '2015-06-02' and then include the result from cdate >= '2016-06-03' and cdate < '2016-06-05' aswell.

[Saleem]

Yes thanks. I worked now. but how do I prepare it in SQL Query, I am selecting the Q1 through Q4 randomly, so how do I prepare the query based on my situation. The query has to be in sql stored procedure.

[Saleem]

Gordon Linoff, Any idea about above.

[Zohar Peled]

I would add some parenthesis for better readability: where ((cdate >= '2015-05-20' and cdate < '2015-06-02') or (cdate >= '2016-06-03' and cdate < '2016-06-05') )

[Saleem]

How do I make the entire where clause in stored procedure to replace with string value. like above I just edited.