CONVERT datetime to time format.

I want to convert a datetime type into and speciically formatted time:
the table contains this:
1899-12-30 10:00:00.000
I want to reformat it to appear like this:
10:00 AM
I want to do this with SQL using a CONVERT of something along that line.
I've been able to find all kinds of date and date/time formats, but not time alone in the above format.
And suggestions?
TIA
</chaz>DATEPART seems to be something that would let you build what you are looking for on the SQL Server side. Better, format the data on the client. The DateTime class should allow you to easily format the date (ToString() for DateTime has overloads that would easily do what you are looking for). The last 7 characters of CONVERT(nvarchar(100),GetDate(),100) would get you 10:00AM (note no space between 00 and AM).|||Thanx!
Formatting on the client side isn't an option here.|||

douglas.reilly wrote:

DATEPART seems to be something that would let you build what you are looking for on the SQL Server side. Better, format the data on the client. The DateTime class should allow you to easily format the date (ToString() for DateTime has overloads that would easily do what you are looking for). The last 7 characters of CONVERT(nvarchar(100),GetDate(),100) would get you 10:00AM (note no space between 00 and AM).

Antoher way would be of I could strip off the that 7 characters that is returned by CONVERT(nvarchar(100),GetDate(),100) and discard the rest.
Another unfortunate aspect is that I have little/no control over the legacy data that this runs on.