SQL query/datetime help: grabbing all results regardless of datetime form

I need some help with a SQL query that's intended to grab results for events
that start in the current day. I have two fields that define the start and
the end of the event called Eve_StartDateTime and Eve_EndDateTime. They
store dates and times using the standard datetime datatype.

The part that I'm having trouble with is that I've inherited a bunch of data
that does not follow any sort of standardized form for datetimes in either
the start or end. You have some events that look like this:

Eve_StartDateTime 10/13/2004 ... Eve_EndDateTime 10/14/2004 [no time
portion]
...and others that look like this
Eve_StartDateTime 10/13/2004 11:00:00 PM ... Eve_EndDateTime 10/14/2004
10:00 PM [time included on both start and end]
....and still others that look like this:
Eve_StartDateTime 10/13/2004 11:00:00 PM ... Eve_EndDateTime 10/14/2004
[time included on start but not end]

With these limits in mind, I'm looking for two queries:

+ A query that captures all events for the current day (or a day assigned to
var activeDate)
+ A query that captures all events that didn't start today, but whose
enddate/time hasn't yet past

I'm using ASP VB and SQL Server 2000. I made a decent stab at a query,
listed below, but it misses events scheduled in the first example form
above, where there is no time component.

Thanks in advance for any help you can offer.

-KF

"SELECT field1, field2, field3 FROM dbo.Events WHERE (Eve_StartDateTime <
'"& CDate(activeDate) &" 23:59:59') AND (Eve_EndDateTime < '"&
CDate(activeDate) &" 23:59:59') AND (Eve_EndDateTime > '"&
CDate(activeDate) &" 00:00:01') AND (Eve_StartDateTime > '"&
CDate(activeDate) & " 00:00:01') ORDER BY Eve_SimpleClass,
Eve_StartDateTime"

Re: SQL query/datetime help: grabbing all results regardless of datetime form

kenfine@u.washington.edu wrote:
> I need some help with a SQL query that's intended to grab results for
> events that start in the current day. I have two fields that define
> the start and the end of the event called Eve_StartDateTime and
> Eve_EndDateTime. They store dates and times using the standard
> datetime datatype.
>
> The part that I'm having trouble with is that I've inherited a bunch
> of data that does not follow any sort of standardized form for
> datetimes in either the start or end. You have some events that look
> like this:
>
> Eve_StartDateTime 10/13/2004 ... Eve_EndDateTime 10/14/2004 [no time
> portion]

When there is no time portion, what is actually stored is:
10/13/2004 00:00:00.000

meaning 12AM of 13-Oct-2004

> With these limits in mind, I'm looking for two queries:
>
> + A query that captures all events for the current day (or a day
> assigned to var activeDate)

Do you mean all events which start before or on the current day and end on
or after the current day?

select column_list from Events
WHERE Eve_StartDateTime <
DATEADD(d,1,CONVERT(char(8),GETDATE(),112))
AND Eve_EndDateTime >=CONVERT(char(8),GETDATE(),112)

> + A query that captures all events that didn't start today, but whose
> enddate/time hasn't yet past

select column_list from Events
WHERE Eve_StartDateTime <
CONVERT(char(8),GETDATE(),112)
AND Eve_EndDateTime >=CONVERT(char(8),GETDATE(),112)

>
> I'm using ASP VB and SQL Server 2000. I made a decent stab at a query,
> listed below, but it misses events scheduled in the first example form
> above, where there is no time component.
>
> Thanks in advance for any help you can offer.
>
> -KF
>
> "SELECT field1, field2, field3 FROM dbo.Events WHERE
> (Eve_StartDateTime < '"& CDate(activeDate) &" 23:59:59') AND
> (Eve_EndDateTime < '"& CDate(activeDate) &" 23:59:59') AND
> (Eve_EndDateTime > '"& CDate(activeDate) &" 00:00:01') AND
> (Eve_StartDateTime > '"& CDate(activeDate) & " 00:00:01') ORDER
> BY Eve_SimpleClass, Eve_StartDateTime"

Don't use dynamic sql. Use stored procedures instead. Here is a link about
passing parameters to stored procedures:

http://tinyurl.com/jyy0

And here are some links about handling dates in asp and sql server:
http://www.aspfaq.com/show.asp?id=2313 vbscript
http://www.aspfaq.com/show.asp?id=2040 help with dates
http://www.aspfaq.com/show.asp?id=2260 dd/mm/yyy confusion


Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.