Query records where time in mutilple ranges in SQL Server

I want to fetch records from "tData" where FooTime column values within a temporary time ranges table "tTime"

tData table:

FooTime            Value
2010-01-01 00:15   3
2010-01-01 00:30   2
2010-01-01 00:45   4
2010-01-01 01:00   5
2010-01-01 01:15   1
2010-01-01 01:30   3
2010-01-01 01:45   4
2010-01-01 02:00   12
2010-01-01 02:15   13
2010-01-01 02:30   12
2010-01-01 02:45   14
2010-01-01 03:00   15
2010-01-01 03:15   3
2010-01-01 03:30   2
2010-01-01 03:45   3
2010-01-01 04:00   5
..........
..........
..........
2010-01-02 00:15   3
2010-01-02 00:30   2
2010-01-02 00:45   4
2010-01-02 01:00   5
2010-01-02 01:15   1
2010-01-02 01:30   3
2010-01-02 01:45   4
2010-01-02 02:00   12
2010-01-02 02:15   13
2010-01-02 02:30   12
2010-01-02 02:45   14
2010-01-02 03:00   15
2010-01-02 03:15   3
2010-01-02 03:30   2
2010-01-02 03:45   3
2010-01-02 04:00   5
..........
..........
..........

tTime table:

StartTime            EndTime
2010-01-01 02:00     2010-01-01 06:00
2010-01-02 02:00     2010-01-02 06:00
....
....
....

According to the above sample data, the following records should be return开发者_运维技巧ed because the time:

FooTime            Value
2010-01-01 02:00   12
2010-01-01 02:15   13
2010-01-01 02:30   12
2010-01-01 02:45   14
2010-01-01 03:00   15
2010-01-01 03:15   3
2010-01-01 03:30   2
2010-01-01 03:45   3
2010-01-01 04:00   5
...
...
2010-01-01 06:00   3


2010-01-02 02:00   12
2010-01-02 02:15   13
2010-01-02 02:30   12
2010-01-02 02:45   14
2010-01-02 03:00   15
2010-01-02 03:15   3
2010-01-02 03:30   2
2010-01-02 03:45   3
2010-01-02 04:00   5
...
...
2010-01-02 06:00   3

Please consider that the time ranges could be different for different day, for example: tTime table:

StartTime            EndTime
2010-01-01 02:00     2010-01-01 06:00
2010-01-02 19:00     2010-01-02 24:00
....
....
....

I'm not near my computer with MS SQL on it but this should do the trick:

SELECT tData.*
FROM 
    tData
    LEFT JOIN tTime
        ON CONVERT(VARCHAR(10), tTime.StartTime, 101) = CONVERT(VARCHAR(10), tData.FooTime, 101)
WHERE
    tData.FooTime BETWEEN tTime.StartTime AND tTime.EndTime

Essentially join the two tables based on the Date (without time) portion of the StartTime and FooDate, then just use a BETWEEN in the WHERE clause.

As I said, I can't test this on this laptop but I'd say it'd work.

Edit: This assumes that there is only 1 day per row in the tTime table as per your example

EDIT: Just remembered that you should be able to use BETWEEN in a JOIN criteria, so this should be better:

SELECT tData.*
FROM 
    tData
    INNER JOIN tTime
        ON tData.FooTime BETWEEN tTime.StartTime AND tTime.EndTime

SELECT FooTime, Value
FROM tData INNER JOIN tTime 
  ON (tData.FooTime >= tTime.StartTime AND tData.EndTime <= tTime.StopTime)

I don't remember if a Between is allowed in a join clause, but if it is:

SELECT FooTime, Value
FROM tData INNER JOIN tTime 
  ON (tData.FooTime BETWEEN tTime.StartTime AND tData.EndTime)