Insert small time into SQL Server 2000 table

Im having a lot of trouble inserting a small time value into a table cell. I gave the cell column the data type 'DateTime', i found i couldnt manually insert a time only value such as '12:30 PM' into a column with 'SmallDateTime'. Something about a "SmallDateTime Overflow Error". However if i enter a similar time value into a table column with the data type 'DateTime' it will happily accept it and leave it as entered.

The real problem seems to be when i try to send a time value to that column with my ASP.NET application. Because it inserts the time value and todays date. So that if i send:

12:30 PM

It will be stored as:

15/11/2003 12:30:00 PM

I only want to store the short time, not the date especially not the date that row was created on because thats useless for the purposes of what my application is trying to achieve and just creates problems down the track when selecting rows.

How can i correct this?Both DateTime and SmallDateTime always have date and time components. Both store the value as a decimal number with the whole number part being the date and the fractional part the time. If you only want the time component set the Date part to 1/1/1900.

When I ran '12:30PM' using datetime and smalldatetime it created the value as '1/1/1900 12:30 PM'. Not sure why you're getting today's date or why it complains entering a smalldatetime with '12:30 PM'

Select Top 1
Cast('12:30 PM' as datetime) as DateTime1230,
Cast('12:30 PM' as smalldatetime) as SmallDateTime1230,
Cast(Cast( '12:30 PM' as datetime) as integer) as IntOfDateTime1230,
Cast(Cast( '12:30 PM' as smalldatetime) as integer) as IntOfSmallDateTime1230,
Cast(Cast( '12:30 PM' as datetime) as float) as FracOfDateTime1230,
Cast(Cast( '12:30 PM' as smalldatetime) as float) as FracOfSmallDateTime1230
From SomeTable

returns

DateTime1230SmallDateTime1230IntOfDateTime1230IntOfSmallDateTime1230FracOfDateTime1230FracOfSmallDateTime1230
1900-01-01 12:30:00.0001900-01-01 12:30:00110.520833333333333370.52083333333333337

|||Ok no worries thats a good explanation. Thanks very much.