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!

Re: Parsing/Selecting DateTime fields 80 80

Let me know if i cant understand your problem.

Re: Parsing/Selecting DateTime fields 80 80

Woww... I missed an easy one there. Thanks Saion!

Re: Parsing/Selecting DateTime fields 80 80

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!

Re: Parsing/Selecting DateTime fields 80 80

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.19 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.