Storing Time information into SQL Server

[Wyatt]

I need to store Time information into my table (SchTimeFrom and SchTimeTo)
but has the following concerns:

Method 1) By setting SchTimeFrom and SchTimeTo as a datetime datatype

The Select statement will first need to convert the SchTimeFrom and
SchTimeTo using DatePart (extracting only Time info out of the DateTime
datatype) which will slow down performance.

In addition, how could I create index on these two fields.

Method 2) By Setting SchTimeFrom and SchTimeTo as Integer/Numeric fields.

By setting SchTimeFrom /To as Integer, my application program (written
in VB) will need to substring and format the data to HH:MM (or from HH:MM to
Integer) format for displaying everytime I query/display/update. And I'll
be restricted to the 24hrs formation (not able to handle AM/PM)

Method 3) By Setting SchTimeFrom / To to String

By setting SchTimeFrom / To to String, I'll have the problem in Method
2) but will be able to handle AM/PM but how should I index the table to
provide maximum performance.

Please Advise.

[David LITOT]

For many reasons I would suggest method 1) as the best.

However should you have any performance concerns one trick would be to split
datetime info into 2 native format fields. ie one datetime field to store
the date part, and one smalldatetime field to hold the time part. Your
application will then have to add the two fields to get the full datetime
back but meanwhile you keep all SQL server functionality for date
manipulation.

Hope this might help.