CODEWITHSUNDEEP
sql-serversql-server-2008t-sqlsql-server-2008-r2
kockiren
kockiren

Reputation: 711

How can I find all results on a date between two dates?

In a SQL Server table there are two dates: a start datetime and an end datetime. Now I want to search for all results on a date time. For example this table:

id|name|starttime|endtime
1|Peter|2014-07-22 07:00|2014-07-22 15:00
2|John|2014-07-22 08:00|2014-07-22 17:00
3|Martin|2014-07-22 07:30|2014-07-22 12:00

Now I want to now all results who match on 2014-07-22 07:50

I take a look to BETWEEN and DATEDIFF but think that doesn't work for it.

Upvotes: 0

Views: 60

Answers (3)

PhillipH
PhillipH

Reputation: 6222

Whats wrong with SELECT * FROM Table WHERE @Parameter BETWEEN startTime AND endTime ?

Upvotes: 1

DavidG
DavidG

Reputation: 119016

Breaking down your logic, you want to find results where your date occurs after starttime and your date occurs before endtime.

Breaking it down further, you have 2 conditions:

  1. YourDate > starttime
  2. YourDate < endtime

So now we can write our SQL:

SELECT *
FROM YourTable
WHERE @YourDate > starttime
AND @YourDate < endtime

Note that you didn't specify if the time you specify can occur exactly on the starttime or end time. Of so, replace < with <= and > with >=.

There are other ways to achieve the same goal but this shows how to break up a problem into smaller parts which is often the path to a solution when writing SQL queries.

Upvotes: 1

mehdi lotfi
mehdi lotfi

Reputation: 11581

Try this:

Select *
From YourTable
Where StartDate <= @YourDate
  and EndDate >= @YourDate

Upvotes: 2

Related Questions