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.
| 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 EndSample Data. Month Start End201610 2016-09-23 2016-10-22201611 2016-10-23 2016-11-22201612 2016-11-23 2016-12-22201701 2016-12-23 2017-01-22I 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..  |
|
|
|
|
|
Search 2 Dates between Date Range