CODEWITHSUNDEEP
sqlsql-server-2005datelogicbetween
CodingIsAwesome
CodingIsAwesome

Reputation: 1966

How to script a SQL "between" that does not include the same day

I am looking to retrieve all records which myDate is between the startDate and 'endDate', but do not return records with exactly the same date. I am using MS SQL 2005.

I have tried:

Select *
From myDatabase
Where empId = 145
and myDate between startDate and endDate

But if myDate was '11/16/2011' the above query would return records which have a startDate and endDate = '11/16/2011' as well. This isn't what I want. I do not want records which have a startDate and endDate = myDate

So I tried:

Select *
From myDatabase
Where empId = 145
and myDate between startDate and endDate
and (myDate <> startDate AND myDate <> endDate)

Will this work for all cases?

Upvotes: 0

Views: 4262

Answers (5)

Jeff Wilbert
Jeff Wilbert

Reputation: 4520

a1ex07 is right in that this will work

myDate > startDate AND myDate < endDate

If you insist on using BETWEEN then this will work also

mydate BETWEEN startDate + INTERVAL 1 DAY AND endDate - INTERVAL 1 DAY

Edit: Just saw the tags for SQL Server not MySQL so the above is for MySQL, the SQL Server equivalent is

myDate BETWEEN DATEADD(DAY, 1, startDate) AND DATEADD(DAY, -1, endDate)

Upvotes: 5

user359040
user359040

Reputation:

Assuming you want to include records where myDate can be either, but not both, startDate and endDate, this should work:

Select *
From myDatabase
Where empId = 145
and myDate between startDate and endDate
and startDate <> endDate

Upvotes: 1

maček
maček

Reputation: 77786

BETWEEN uses >= and <=

You will just need to use > and < if you don't want it to match the end points.

SELECT *
FROM myDatabase
WHERE empId = 145
AND myDate>startDate and myDate<startDate;

Upvotes: 0

Ken White
Ken White

Reputation: 125729

You need to use something other than BETWEEN. Try this instead:

SELECT *
FROM myDatabase
WHERE empID = 145
AND (myDate > startDate) and (myDate < endDate)

Upvotes: 0

a1ex07
a1ex07

Reputation: 37382

I don't understand why you have to use BETWEEN... Just myDate>startDate AND myDate<endDate will do the job.

Upvotes: 4

Related Questions