A date interval with a date interval query

spinto · May 14th, 2004, 09:49 AM

I'm trying to figure out a query that will return records if any dates between 2 user entered dates fall with a date range stored in the db. Lets say the 2 user dates are called UDate1 and UDate2 and the db date range is called DDate1 and DDate2. Any date between UDate1 and UDate2 must match any date between DDate1 and DDate2. This all needs to be done with a query.

happygv · May 14th, 2004, 10:11 AM

I think you are wanted to retrieve records that are falls in a range of DATEs that's given.

EG:
SELECT RECORDS FROM TABLE
WHERE DATEFIELD BETWEEN StartDate and EndDate

Something like that?

For this it is enough if you have only one DATEFIELD in your database, if I am right.

This is how you got to go about.

Code:

Select * from <TABLENAME>
Where <DATEFIELD> between convert(datetime,'<DATE1>') and convert(datetime,'<DATE2>')

OR

Code:

Select * from <TABLENAME>
Where <DATEFIELD> >= convert(datetime,'<DATE1>') and 
<DATEFIELD> <= convert(datetime,'<DATE2>')

Replace <TABLENAME> with your table name, same way do that for <DATEFIELD>, <DATE1> and <DATE2> with relevant data.

Cheers!

-Vijay G

spinto · May 14th, 2004, 10:41 AM

Actually there are two date fields in the database so it needs to search with a date range in a database. Any date within one date range must match any date within another date range.

happygv · May 14th, 2004, 10:53 AM

As it is named this way, DDate1 & DDate2, I can't really understand what it means? That was why the doubt arised.

Quote:

quote:Any date between UDate1 and UDate2 must match any date between DDate1 and DDate2.

What is the connection between these two date Fields, as you say you wanted to get records between these dates.

[quote]Any date within one date range must match any date within another date range.[quote]

I am not sure what you are trying to explain there? Can you rephrase that so as to understand it better and help you?

-Vijay G

spinto · May 14th, 2004, 11:14 AM

Sorry for not being clear. A user must enter a date range lets say 2/1/2004 to 2/20/2004. When they hit submit it must do a query against a date range in the database. Lets say there are two records in the DB

Date1 | Date2
1/25/2004 | 2/5/2004
1/4/2004 | 3/4/2004

Both these records will return records because dates within the date range in the database overlap with the dates entered by the user. Specifically for the first record the dates 2/2,2/3,2/4,2/5 fall with the date range specified by the user. In the second record the dates 2/1-2/20 fall within the user entered dates.

Select * from <TABLENAME>
Where <Any Date between DATEFIELD1 and DATEFIELD2> = <Any Date between USERENTEREDDATE1 and USERENTEREDDATE2>

happygv · May 14th, 2004, 11:27 AM

Ahhh... Now that's clear about what you are looking for.

As you say user enters date USERENTEREDDATE1 and USERENTEREDDATE2 should be converted to datetime datatype, if required.

Use

Code:

Convert(datetime, USERENTEREDDATE1)

Code:

Convert(datetime, USERENTEREDDATE2)

in the following query where ever required, if that doesn't produce the expected result. Also see to that the USERENTEREDDATE1 and USERENTEREDDATE2 are in the same format as that of the values in the database.

Code:

Select * from <TABLENAME>
Where 
(DATEFIELD1 <= USERENTEREDDATE1 and DATEFIELD1 < USERENTEREDDATE2)
and 
(DATEFIELD2 >= USERENTEREDDATE1 and DATEFIELD2 >= USERENTEREDDATE2)

I haven't tried this out in my PC here. But you can give a try.

Code:

Select * from <TABLENAME>
Where 
(USERENTEREDDATE1 between DATEFIELD1 and DATEFIELD2) 
and 
(USERENTEREDDATE2 between DATEFIELD1 and DATEFIELD2)

Hope that helps.
Cheers!

-Vijay G

spinto · May 14th, 2004, 12:20 PM

That's not going to work. What if a user enters 2/1 - 2/27 and there are dates in the database 1/2 - 2/25. The record should come up because the database dates 2/1 - 2/25 overlap with the user entered dates 2/1 - 2/27. I might have come up with a solution though. Let me know what you think:

SELECT * FROM <TABLENAME>
WHERE
(USERENTERDATE1 <= DATEFIELD1 AND USERENTERDATE2 >= DATEFIELD2) OR
(USERENTERDATE1 >= DATEFIELD1 AND USERENTERDATE1 <= DATEFIELD2)

happygv · May 15th, 2004, 04:26 AM

Ok, I didnot think about that overlapping. That should be perfect.

Cheers!

-Vijay G