Ladies / Gentlemen


        I need to be able to store only the 'Time' that an event happens not
the 'DateTime' how can this be done?  I need to make sure that 'Dates' do
not come into play as I will be doing queries such as this.


        Select *
        Where StartTime <= Convert(DateTime, GetDate(), 108)
            and EndTime >= Convert(DateTime, GetDate(), 108)

        or

        Select *
        Where Convert(DateTime, GetDate(), 108) Between StartTime and
EndTime


        Also should I store the StartTime and EndTime as DateTime fields

Keep everything in datetime format. If you want to compare only the times,
then

select * from [yourtable]
where DATEADD(dd, DATEDIFF(dd, starttime, GETDATE()), starttime) <=GETDATE()
and DATEADD(dd, DATEDIFF(dd, endtime, GETDATE()), endtime) >= GETDATE

Show quoteHide quote

Are you absolutely you don't need a Date portion?  If something happened at
2:00PM 2 years ago will it really be the same as something that happened at
2:00PM today?  With your example that is how they will be treated.  And is
seconds enough accuracy?  If that is true then you have two choices.  One is
to store them as Small Datetime and always append the same date portion on
it.  I would use the default of Jan 01 1900.  Then make sure the times you
are comparing against have the same Date portion and you can use an index
with no problem.  The other is to use an INT datatype and store the time as
hhmmss

1:27:00 PM would be  132700
9:33:23 AM would be  093323

This format is also searchable but may require a little more work to get it
into and out of the right format.

Show quoteHide quote

Take note that if you store just time info in a datetime field that you
are defaulting to a date of 1899-12-30.  You wont see this in
Enterprise Manager but it is there.  You can verify this in query
analyzer.  I have an app that only needs the time info and I pull the
time out in a UDF.

Nope, SQL Server defaults to 1900-01-01. But EM doesn't understand that and will assume that
1899-12-30 means "no date".

If one want to take the "always the same date" approach, you must decide on what same date that
*all* values *must* have. And to ensure that, you use a CHECK constraint. In some cases, you also
use a DEFAULT constraint.

In the SQL Server world, most uses 1900-01-01 as the "always the same date" as this is the database
engine gives you if you do not enter a time value. But again, a CHECK constraint is needed as some
tools actually specify some other date for you (like EM does).

Show quoteHide quote

You could use a time only UDT and get time specific operators on it.  You
could use this or make your own.
http://channel9.msdn.com/ShowPost.aspx?PostID=147390

Show quoteHide quote

Why is this statement blocking...
NESTED SQL QUESTION and aggregate function
CLR Triggers and inserted table
SQL script, split digits and letters
Concatenating:www.aspfaq.com/show.asp?id=2529 Is the glass half full or half empty or..?