Date range with a date range - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Date range with a date range

Author

Topic

Dobly
Starting Member

16 Posts

Posted - 2011-03-23 : 02:39:34

I am building a search page on my project.

On the search form the user can enter a date range.

In the 'Activity' database table they are searching against there is two columns of interest.

Date_Start
Date_End

I need to return Activities that are occurring any time along the date range of the Date_Start and Date_End.

For example.

The table might have an activity with these date values.

Date_Start Date_End
2011-2-10 2011-2-16

If the user searches for activities in the range of...

From: 2011-2-12 To: 2011-2-13

I need the activity above returned.

Any tips would be much appreciated.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-03-23 : 03:14:58

SELECT * FROM ActivityTable WHERE Date_Start < '2011-02-12' AND Date_End > '2011-2-13'

Vaibhav T

If I cant go back, I want to go fast...

Sachin.Nand

2937 Posts

Posted - 2011-03-23 : 03:15:13

Use "between" clause in the where clause of your query.

PBUH

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-03-23 : 04:27:40

Between will not work as OP wants that date range supplied should be within Date_Start and Date_End.

Vaibhav T

If I cant go back, I want to go fast...

Sachin.Nand

2937 Posts

Posted - 2011-03-23 : 06:19:25

@Vaibhav

Yes I missed on that one.

Also I think your query condition


WHERE Date_Start > '2011-02-12' AND Date_End < '2011-2-13'

and not this


WHERE Date_Start < '2011-02-12' AND Date_End > '2011-2-13'

PBUH

NeilG
Aged Yak Warrior

530 Posts

Posted - 2011-03-23 : 07:44:59

quote:
Originally posted by vaibhavktiwari83

Between will not work as OP wants that date range supplied should be within Date_Start and Date_End.

Vaibhav T

If I cant go back, I want to go fast...

That wouldnt work, the between operator can only work with one column i.e. startdate it can't work with startdate and enddate in the same clause

-----------------------------------------------
Learning something new on SQL Server everyday.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-03-23 : 07:49:05

@Sachin :
But I think

Date_Start < '2011-02-12' AND Date_End > '2011-2-13'

will work otherwise that activity mention by OP will not come as output.

Rethink on it.

Vaibhav T

If I cant go back, I want to go fast...

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-03-23 : 12:06:44

A simple test will show how each where clause will work:

--Sample data
DECLARE @T TABLE (Date_Start DATETIME, Date_End DATETIME)

INSERT @T (Date_Start, Date_End)
VALUES
('20110210', '20110216'),
('20110310', '20110316'),
('20110110', '20110116')

--Tests
SELECT *
FROM @T
WHERE Date_Start > '2011-02-12' AND Date_End < '2011-2-13'


SELECT *
FROM @T
WHERE Date_Start < '2011-02-12' AND Date_End > '2011-2-13'

BruceT
Yak Posting Veteran

78 Posts

Posted - 2011-03-23 : 14:36:38

What if the user searches for activities in the range of...

From: 2011-2-12 To: 2011-2-20

do you still need the activity

Date_Start Date_End
2011-2-10 2011-2-16

returned or just activities that are completely encompassed within the search dates?

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-03-24 : 03:25:23

@Lamprey : Good Work

@Bruce : Requirement of OP is that search dates should be within the brackets of Date_Start and Date_End not the vice versa.

Vaibhav T

If I cant go back, I want to go fast...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-03-24 : 04:29:29

Also make sure to use unambigious date formats
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/03/understanding-datetime-column-part-ii.aspx

Madhivanan

Failing to plan is Planning to fail

Dobly
Starting Member

16 Posts

Posted - 2011-03-24 : 19:26:40

quote:
Originally posted by BruceT

What if the user searches for activities in the range of...

From: 2011-2-12 To: 2011-2-20

do you still need the activity

Date_Start Date_End
2011-2-10 2011-2-16

returned or just activities that are completely encompassed within the search dates?

I should have been more clear. Yes, If they search for 12 to 20th I need the activity.

Any overlap of date needs to be returned.

This one is doing my head in.

Dobly
Starting Member

16 Posts

Posted - 2011-03-24 : 21:00:57

I think I have it.. .

This seems a bit long winded but I think it covers it.




DECLARE @T TABLE (Date_Start DATETIME, Date_End DATETIME)

INSERT @T (Date_Start, Date_End)
VALUES
('20110210', '20110216'),
('20110211', '20110212'),
('20110213', '20110214')

SELECT *
FROM @T
WHERE (Date_Start <= '2011-02-12' AND Date_End >= '2011-2-13')
      OR
      (Date_Start >= '2011-02-12' AND Date_Start <= '2011-2-13')
      OR
      (Date_End >= '2011-02-12' AND Date_End <= '2011-2-13')

Basically I'm testing for the dates in between then checking the start and end date to see if they fall into the entered range.

I need to so some more testing but I think I have it.

Thanks all who helped with this one.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-03-25 : 03:58:10

Wouldn't this give the same result?

SELECT	*
FROM	@T
WHERE	Date_Start <= '20110213'
	AND Date_End >= '20110212'

N 56°04'39.26"
E 12°55'05.63"

Dobly
Starting Member

16 Posts

Posted - 2011-03-27 : 19:59:01

quote:
Originally posted by Peso

Wouldn't this give the same result?
SELECT *
FROM @T
WHERE Date_Start <= '20110213'
AND Date_End >= '20110212'

N 56°04'39.26"
E 12°55'05.63"

No it would not. It would work in the case above, but not in the following case

With the activity dates of the 10th to the 16th, a search with the dates of 12th to 20th would fail as the Date_End is not >= the 20th.

So yours would work when the entered dates are between the activity dates, but not when the entered date crosses the start or end date of the activity.

Hence the need for 3 conditions. One for the date range within date range, one with the Date start within the entered range and one with the date end within the entered range.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources