records between two user defined dates
this is a followup question on a previous post:
https://www.experts-exchange.com/questions/21062326/determining-if-certain-dates-are-within-a-date-range.html
this gives all records with a start or end date between the two user defined dates:
**************************
select *
from table
where dateserial(sYear,sMonth,sD
**************************
i want to also add the reverse of this, meaning i want to check for
"all records where the user defined start date is between the record's start date and end date
AND
all records where the user defined end date is between the record's start date and end date.
"
how to do this?
https://www.experts-exchange.com/questions/21062326/determining-if-certain-dates-are-within-a-date-range.html
this gives all records with a start or end date between the two user defined dates:
**************************
select *
from table
where dateserial(sYear,sMonth,sD
**************************
i want to also add the reverse of this, meaning i want to check for
"all records where the user defined start date is between the record's start date and end date
AND
all records where the user defined end date is between the record's start date and end date.
"
how to do this?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Meps - that is correct you would only get one record in your example which should be the desired result of
"all records where the user defined start date is between the record's start date and end date
AND
all records where the user defined end date is between the record's start date and end date."
There is no other record in your sample that meets these conditions so only one record should be returned which is how I wrote it.
entrance is correct in that you need to do something with the dates, but the single quote only works for SQL NOT access and as sagardeo did not say which database I did not put that in.
If it is access then you need to use # around the date and NOT single quotes. But to get # you need to escape it so it would be :
SELECT * FROM table
WHERE RecordStartDate <= ###UserStartDate###
AND RecordEndDate >= ###UserStartDate###
AND RecordStartDate <= ###UserEndDate###
AND RecordEndDate >= ###UserEndDate###
Also you need to decide if time should be taken into acount or not. But these minor tweaks all depend on what you want. The idea is still using the solution I posted initially of :
SELECT * FROM table
WHERE RecordStartDate <= #UserStartDate#
AND RecordEndDate >= #UserStartDate#
AND RecordStartDate <= #UserEndDate#
AND RecordEndDate >= #UserEndDate#
And then just making sure you set your parameters correctly for your database
"all records where the user defined start date is between the record's start date and end date
AND
all records where the user defined end date is between the record's start date and end date."
There is no other record in your sample that meets these conditions so only one record should be returned which is how I wrote it.
entrance is correct in that you need to do something with the dates, but the single quote only works for SQL NOT access and as sagardeo did not say which database I did not put that in.
If it is access then you need to use # around the date and NOT single quotes. But to get # you need to escape it so it would be :
SELECT * FROM table
WHERE RecordStartDate <= ###UserStartDate###
AND RecordEndDate >= ###UserStartDate###
AND RecordStartDate <= ###UserEndDate###
AND RecordEndDate >= ###UserEndDate###
Also you need to decide if time should be taken into acount or not. But these minor tweaks all depend on what you want. The idea is still using the solution I posted initially of :
SELECT * FROM table
WHERE RecordStartDate <= #UserStartDate#
AND RecordEndDate >= #UserStartDate#
AND RecordStartDate <= #UserEndDate#
AND RecordEndDate >= #UserEndDate#
And then just making sure you set your parameters correctly for your database
Your answer is complite!:-)
---Pinal