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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Search 2 Dates between Date Range
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Gerald30
Yak Posting Veteran

Philippines
62 Posts

Posted - 10/25/2016 :  21:59:58  Show Profile  Reply with Quote
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..
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000