Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Search 2 Dates between Date Range

Author  Topic 

Gerald30
Yak Posting Veteran

62 Posts

Posted - 2016-10-25 : 21:59:58
Hello Team,

I have table with the following columns.
1.) Month
2.) Coverage Start
3.) Coverage End

Sample Data.
Month Start End
201610 2016-09-23 2016-10-22
201611 2016-10-23 2016-11-22
201612 2016-11-23 2016-12-22
201701 2016-12-23 2017-01-22

I want to retrieve the Months based on the given Start and End date of the User.
Example if the User gives the date from 10/10/2016 to 11/15/2016 it should return the months of 201610 and 201611.

I have created 3 scripts but each has its own limitation.

1st one is

"
select
*
from E_ReportCutOff
where
RCO_Cutoffstart between '10/10/2016' and '12/25/2016'
or RCO_Cutoffend between '10/10/2016' and '12/25/2016'

"
The Problem with this one is it is good for the dates with more than 1 month. It cannot return a value if the given date is less than a month.


2nd Script.

"
select
*
from E_ReportCutOff
where RCO_Cutoffstart <= '10/10/2016'
and RCO_Cutoffend >= '10/15/2016'
"
This is is good if the dates given is less than a month.

3rd script.

"
select
*
from E_ReportCutOff
where
'10/10/2016' between RCO_CutOffStart and RCO_CutOffEnd
or
'12/25/2016' between RCO_CutOffStart and RCO_CutOffEnd
"

This script almost solves the limitation of the 2 scripts above the problem is it only shows the 1st and the last month and it does not include the month in between of the dates.

I have actually combine the first 2 scripts to solve the problem but I`m still looking for a another way (shorter/more efficient statement) to solve this.

"
select
*
from E_ReportCutOff
where (RCO_Cutoffstart <= '10/10/2016'
and RCO_Cutoffend >= '12/25/2016')
or
(
(RCO_Cutoffstart between '10/10/2016' and '12/25/2016')
or (RCO_Cutoffend between '10/10/2016' and '12/25/2016'))

"

Thank you..
   

- Advertisement -