Help with query !!! (I'm desperate)

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

General SQL Server Forums

New to SQL Server Programming

Help with query !!! (I'm desperate)

Author

Topic

runs
Starting Member

2 Posts

Posted - 2012-01-25 : 12:24:58

I need a selection of registers by range of dates.

Example:
If a enter a query from 01/01/2012 to 10/01/2012 over a set of records with two field dates (init date & end date), I want the following matches:

01/01/2012 - 10/01/2012 -> Yes
30/12/2011 - 02/01/2012 -> Yes
02/01/2012 - 05/01/2012 -> Yes
08/12/2011 - 12/01/2012 -> Yes
10/01/2012 - 12/12/2012 -> Yes

In short, that my query searchs for records that "touch" the desired range of dates.

I try a lot of combinations, whitout no right result.

Thanks in advance.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-25 : 12:43:32

[code]DECLARE @startDate DATETIME, @endDate DATETIME;
SET @startDate = '20120101';
SET @endDate = '20120110';

SELECT
init_date,
end_date
-- ,other columns
FROM
YourTable
WHERE
init_date <= @endDate
AND end_date >= @startDate;[/code]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-01-25 : 12:47:15

something like this should work:

SET DATEFORMAT DMY;

DECLARE @Table TABLE(StartDate DATE, EndDate DATE);

INSERT @Table (StartDate, EndDate)
VALUES
('01/01/2010', '31/12/2012'),
('01/01/2012', '10/01/2012'),
('30/12/2011', '02/01/2012'),
('02/01/2012', '05/01/2012'),
('08/12/2011', '12/01/2012'),
('10/01/2012', '12/12/2012'),
('10/02/2012', '12/12/2012')

DECLARE @FromDate DATE = '01/01/2012'; 
DECLARE @ToDate DATE = '10/01/2012';

SELECT *
FROM @Table AS T
WHERE 
    @FromDate BETWEEN StartDate AND EndDate
    OR @ToDate BETWEEN StartDate AND EndDate;

runs
Starting Member

2 Posts

Posted - 2012-02-22 : 17:19:06

Thanks you all a lot!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources