Database Gurus: Should Ending Dates be inclusive or exclusive.

[ICRS]

I think all dates including ending dates should be inclusive. My coworker thinks we should set the database up to be exclusive. Which is best practice?

Example: Item A ends and then Item B immediatly begins.

Inclusive:
Item A:
Start Date: 11/1/05
End Date: 11/1/08
Item B:
Start Date: 11/2/08
End Date: Null

Exclusive:
Start Date: 11/1/05
End Date: 11/2/08

Item B:
Start Date: 11/2/08
End Date: Null

 

[her209]

Inclusive

 

[mugs]

Originally posted by: her209
Inclusive

 

[Jeff7181]

In your exclusive example, item A would actually be starting on 11/2/05 and ending on 11/1/08 and item B would actually be starting on 11/3/05.

But it doesn't really matter since it should be inclusive anyway.

 

[nakedfrog]

What field type is this?

 

[ICRS]

Originally posted by: nakedfrog
What field type is this?

In 2005, I would think Datetime.

 

[nakedfrog]

Originally posted by: ICRS

Originally posted by: nakedfrog
What field type is this?

In 2005, I would think Datetime.

So you're arguing over 00:00 or 23:59?

 

[mugs]

Originally posted by: ICRS

Originally posted by: nakedfrog
What field type is this?

In 2005, I would think Datetime.

I assume 2005 = SQL Server 2005. I haven't worked in SQL Server in a long time, but if you're using a datetime field I assume you're storing a date AND a time? So if you wanted your search end date to be 11/1/08 inclusive, you'd want to search for everything less than 2008-11-02 00:00:00. If you searched for everything <= 11/1/08, you'd be searching for everything <= 2008-11-01 00:00:00; you'd be excluding all but the first second of that day.

That should be invisible to the end user though. If the end user wants to search for everything up to 2008-11-01 inclusive, that's the way it should work. That's the way all users will expect it to work, and the programmers need to make it worth that way. Given that you're an idiot, I'm assuming you're an end user.


Edit: to clarify what I said above -
I think what's happening here is someone is creating a user interface where someone enters a date search range, and when the coworker uses those dates in a query on a datetime field it is exclusive of the end date. That is, if SQL Server works the way Oracle works, if you query on a datetime with just a date it will be interpreted as the beginning of the day. The DB works that way, thus this coworker thinks it should be so for whatever he's working on. He is wrong. It needs to work the way the user needs/expects it to work, which is inclusive end dates. Simple solution - add a day to the end date and use < instead of <=. I assume SQL Server has a function to add a day to a date, but it would be pretty hilarious to watch the guy take the month/day/year and try to figure out what the next day is factoring in leap years.

 

[ICRS]

Originally posted by: nakedfrog

Originally posted by: ICRS

Originally posted by: nakedfrog
What field type is this?

In 2005, I would think Datetime.

So you're arguing over 00:00 or 23:59?

We don't actually care about time. There is no Date type in MSSQL.

 

[ICRS]

Originally posted by: mugs

Originally posted by: ICRS

Originally posted by: nakedfrog
What field type is this?

In 2005, I would think Datetime.

I assume 2005 = SQL Server 2005. I haven't worked in SQL Server in a long time, but if you're using a datetime field I assume you're storing a date AND a time? So if you wanted your search end date to be 11/1/08 inclusive, you'd want to search for everything less than 2008-11-02 00:00:00. If you searched for everything <= 11/1/08, you'd be searching for everything <= 2008-11-01 00:00:00; you'd be excluding all but the first second of that day.

That should be invisible to the end user though. If the end user wants to search for everything up to 2008-11-01 inclusive, that's the way it should work. That's the way all users will expect it to work, and the programmers need to make it worth that way. Given that you're an idiot, I'm assuming you're an end user.

I am a programmer.

 

[mugs]

Originally posted by: ICRS

Originally posted by: mugs

Originally posted by: ICRS

Originally posted by: nakedfrog
What field type is this?

In 2005, I would think Datetime.

I assume 2005 = SQL Server 2005. I haven't worked in SQL Server in a long time, but if you're using a datetime field I assume you're storing a date AND a time? So if you wanted your search end date to be 11/1/08 inclusive, you'd want to search for everything less than 2008-11-02 00:00:00. If you searched for everything <= 11/1/08, you'd be searching for everything <= 2008-11-01 00:00:00; you'd be excluding all but the first second of that day.

That should be invisible to the end user though. If the end user wants to search for everything up to 2008-11-01 inclusive, that's the way it should work. That's the way all users will expect it to work, and the programmers need to make it worth that way. Given that you're an idiot, I'm assuming you're an end user.

I am a programmer.

Oh wow. I edited. Does my edit describe what the issue is?

 

[Muse]

Originally posted by: Jeff7181
In your exclusive example, item A would actually be starting on 11/2/05 and ending on 11/1/08 and item B would actually be starting on 11/3/05.

But it doesn't really matter since it should be inclusive anyway.

Inclusive. It's not really a technical issue, just a comprehension issue. For instance, if you hear that a sale is from 11/1/08 to 11/27/08 you figure that you can buy on Thanksgiving. This isn't a programming issue. However, the same period would work in FoxPro if you used BETWEEN(DATE()+2,{11/01/2008},{11/27/2008}), which asks the question can I buy it on Thanksgiving?

 

[mugs]

Originally posted by: Muse

Originally posted by: Jeff7181
In your exclusive example, item A would actually be starting on 11/2/05 and ending on 11/1/08 and item B would actually be starting on 11/3/05.

But it doesn't really matter since it should be inclusive anyway.

Inclusive. It's not really a technical issue, just a comprehension issue. For instance, if you hear that a sale is from 11/1/08 to 11/27/08 you figure that you can buy on Thanksgiving.

Yeah, exactly. Trying to dictate counter-intuitive functionality to the user is a sign of a bad programmer.

Then again, considering how many people here think "last Monday" was yesterday and "next Wednesday" is tomorrow, I wouldn't rule out the possibility that this guy thinks that sale would end the day before Thanksgiving. :Q

 

[dbk]

Dates are always inclusive, imo.

 

[Jeff7181]

Originally posted by: mugs

Originally posted by: Muse

Originally posted by: Jeff7181
In your exclusive example, item A would actually be starting on 11/2/05 and ending on 11/1/08 and item B would actually be starting on 11/3/05.

But it doesn't really matter since it should be inclusive anyway.

Inclusive. It's not really a technical issue, just a comprehension issue. For instance, if you hear that a sale is from 11/1/08 to 11/27/08 you figure that you can buy on Thanksgiving.

Yeah, exactly. Trying to dictate counter-intuitive functionality to the user is a sign of a bad programmer.

Then again, considering how many people here think "last Monday" was yesterday and "next Wednesday" is tomorrow, I wouldn't rule out the possibility that this guy thinks that sale would end the day before Thanksgiving. :Q

When is the Monday before last?

 

[speg]

Inclusive. If something ends on a particular day, then it's still applies for that day, and thus that day should be included.

 

[Zim Hosein]

---

Are you working on this DB on a Mac or Win rig ICRS?

Zim Hosein

Off Topic Moderator.

 

[AkumaX]

DateTime!

 

[rh71]

I remember having to code around the between operator because it was exclusive (if memory serves). SQL Server 2000, perhaps even 2005.

Not a DBA, didn't know it could be set.