Member Avatar for cheapterp

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!

  • 3 Contributors
  • 4 Replies
  • 169 Views
  • 4 Days Discussion Span
  • Latest Post Latest Post by mail2saion

Recommended Answers

All 4 Replies

Member Avatar for mail2saion

USE BETWEEN CLAUSE. LIKE: WHERE A BETWEEN C AND D .
Let me know if i cant understand your problem.

Member Avatar for cheapterp

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

Member Avatar for SQL_n00b

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!

TIA!

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!

Member Avatar for mail2saion

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 developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.