I have a table off which I need to parse out the data using certain criteria. The table contains 3 datetime columns called 'ReadingTakenOn', 'StartedOn', and 'StoppedOn'

Here's the criteria for selecting the records:
The Dates in the 'ReadingTakenOn' column should lie between the Start Date and Stop Dates (both inclusive)

Now, I have to further pick out records from the above selected ones based on the 'Time' values as:
If the 'ReadingTakenOn' Time lies between the 'StartedOn' and 'StoppedOn'. I am not sure if I have been terribly clear on that, so here's an example:

Let's say the ReadingTakenOn column begins on 10/15/2006 08:00 and goes all the way down to 11/15/2006 18:30.

Now assume the value for 'StartedOn' is 10/26/2006 11:00 and 'StoppedOn' is 11/15/2006 16:00.

Now I want to obtain records from 10/26/2006 11:00 to 11/15/2006 16:00 and ignore records that occur before and after these dates+times.

I am struggling to write the WHERE clause for the SQL query to achieve this

Just FYI, I am using MS-SQL 2005.

Thanks in advance!

8 Years
Discussion Span
Last Post by mail2saion

I have a query similar to the original poster's, so I thought I might post it here.

I want to do exactly what he/she is trying to- the only difference being that my data comes from 2 different tables. The date and time columns that correspond to the 'StartedOn' and 'StoppedOn' columns are in one table and the column corresponding to 'ReadingTakenOn' is in another table. The rest of the objective is exactly the same.

Any help is appreciated!


P.S: I understand might have hijacked the thread here and if that is a serious flouting of the forum rules, I will be happy to start a new thread!


Let start is in table A and stopeed is in table b then first apply join then apply between clause. LIKE SELECT A.*,B.* FROM A INNER JOIN B ON A.ID=B.ID WHERE GETDATE() BETWEEN A.START AND B.STOP

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.