help in sql query

Question

i have a table that has id,fromdate and todate as columns. i need to select the ids that is between 2dates specified by the user. for example:

ID   FromDate   ToDate
1    2010-01-10 2010-01-13
2    2009-03-20 2010-01-09

so if the user entered datefrom=2000-00-00 and dateto=2009-03-21 i return :

ID
2

if the user entered from=2009-00-00 to=2011-00-00 i return :

ID
1
2

note i am using MS server 2008

Answer 1

SELECT * FROM TableName
WHERE FromData>@startdate AND ToData<@enddate

Answer 2

try this:

--needed for each query run
DECLARE @YourTable  table     (RowID int, FromDate datetime, ToDate datetime)
DECLARE @datefrom  datetime
       ,@dateto    datetime

INSERT INTO @YourTable VALUES (1,'2010-01-10', '2010-01-13')
INSERT INTO @YourTable VALUES (2,'2009-03-20', '2010-01-09')

first example

--be sure to use the DECLAREs and INSERTs from above with this
SELECT @datefrom='2000-01-01'
      ,@dateto='2009-03-21'

--query to that only checks if the FromDate is between the given @datefrom and @dateto, which is necessary for the OPs first example
SELECT 
    *
    FROM @YourTable
    WHERE FromDate>=@datefrom AND FromDate<@dateto+1

OUTPUT:

RowID       FromDate                ToDate
----------- ----------------------- -----------------------
2           2009-03-20 00:00:00.000 2010-01-09 00:00:00.000

(1 row(s) affected)

second example

--same query as above, but run with example two dates
--be sure to use the DECLAREs and INSERTs from above with this
SELECT @datefrom='2009-01-01'
      ,@dateto='2011-01-01'

SELECT 
    *
    FROM @YourTable
    WHERE FromDate>=@datefrom AND FromDate<@dateto+1

OUTPUT:

RowID       FromDate                ToDate
----------- ----------------------- -----------------------
1           2010-01-10 00:00:00.000 2010-01-13 00:00:00.000
2           2009-03-20 00:00:00.000 2010-01-09 00:00:00.000

(2 row(s) affected)

Alternate queries, to make sure that the entire FromDate+ToDate range is within the given @datefrom and @dateto values use this query:

--be sure to use the DECLAREs and INSERTs from above with this    SELECT @datefrom='2009-03-22'
      ,@dateto='2011-01-01'

SELECT 
    *
    FROM @YourTable
    WHERE FromDate>=@datefrom AND ToDate<@dateto+1

OUTPUT:

RowID       FromDate                ToDate
----------- ----------------------- -----------------------
1           2010-01-10 00:00:00.000 2010-01-13 00:00:00.000

(1 row(s) affected)