Yet another date compare question

[TMac42]

I've got events in my database. Some start at a certain time, so we save the time. Others do not specify a time, so only the date is saved. All of these entries in the db are datetimes.

In my application, if the user wants events for just one day, that date (ex: '10/17/2005') is passed in to my stored procedure. Then my code looks something like this:

AND EventStartDateTime <= ' + @RequestedDate + ' AND EventEndDateTime >= ' + @RequestedDate

I'm asking for dates in front or after because I still want to catch those events that are an ongoing event that are still taking place on the requested date.

Problem is, if a date is scheduled for '10/17/2005 9:00:00 AM' and the user requests all events for '10/17/2005', the above entry does not show up because it has no time value and is thought to be midnight.

Since I still want to include the range-type events... how can I get around this in SQL? Any thoughts?

Thanks so much!

 

[MDXer]

The following should work based upon the start time the Cast(Convert(varchar,@sDate,101) as datetime) takes you entry date and makes it midnight based, the second criteria in the between does the same but then adds 86399 seconds making the endtime 11:59:59PM.

If you need it for the Event End time you should be able to add a second criteria to your where clause using OR.

Declare @sDate datetime

Select
EvenCol
From tblEvent
Where
EventStartDateTime between Cast(Convert(varchar,@sDate,101) as datetime) and DateAdd(ss,86399,Cast(Convert(varchar,@sDate,101) as datetime))

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[vongrunt]

Better forget BETWEEN or >=/<= and use half-open interval. See:

<self-advertising>
faq183-5842, tip #3
</self-advertising>

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)