Whats the best MS SQL datatype to store the time of day independently from the date ?

The best way would be to combine the values from your two inputs and put it into a single datetime value!
Because really, it's a date and time (datetime)

Its not possible to save only time part in datetime or smalldatetime datatype.

You can also check the msdn discussion on this topic from link:
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/76281bc8-a97a-46df-a78a-4864ba4cdb1b

Hope this will help you

There are plenty of queries shown in EE that teach you how to extract just the date or time portion from a datetime, but normally, it is a sort of "timestamp" or "chronology" or "schedule" so it does not make sense to store separately.

E.g. select * from events order by eventdatetime  -- makes sense
E.g. select * from events order by eventdate, eventtime  -- hmm... not optimised

E.g. select * from events where eventdatetime >= dateadd(dd, -1, getdate()) -- within last 24 hours
The equivalent with the split fields would be much more tricky.

However, if you must store them separately, then I would still recommend 2 fields, both datetime because it is the datatype for date/time/datetime values.

Just check this out:

http://weblogs.sqlteam.com/jeffs/archive/2007/10/31/sql-server-2005-date-time-only-data-types.aspx

-Kusala

use datetime for both...

ensure that the date is stored without a time...   use midnight 00:00:00.000
 or just store it as a "date" component e.g. '20100426'
and ensure that the time is stored without a date ... use 19000101

that way you can still use the Date/Time functions to extract the individual components...

(probably should have an insert/update tigger to conform the columns to only date/time usage...  those developers will take there eyes off the ball at some stage )


alternatively the 2008 version of sql server supports separate date and time datatypes...

I like cyberkiwis angle but I see that then Im taking the two entered values from the page and properly combining them into a single field.  When making reports or read only pages I would split the field into the seperate date and time components.  

The only reason to possibly store them into different fields was for the sake of doing some date/time math but in retrospect that could be done anyhow, right

I'll use dsrivallabh's suggestion with the GetTime function too - but should I use cast to change the data type from varchar to datetime before saving when I use that function?

Yes..you can to that if the date is also stored separately then you can just get the date part also form a varchar string and CAST it to DATETIME

Thank you much!

Although I wont use the following approach I thought it was interesting:
http://weblogs.sqlteam.com/jeffs/archive/2007/10/31/sql-server-2005-date-time-only-data-types.aspx