Earlier on I wrote about how you can Select just the date part from a DateTime or SmallDateTime data type. (See [story:t-sql-date-without-time])
Using the CONVERT( … , … , style ) function we can specify the style attribute, that lets us specify to SQL Server (using style 14 or 114) that we just want the time portion.
SELECT CONVERT( CHAR(8), GetDate(), 14)
By changing the number of CHAR characters you can select the time with or without milliseconds and seconds.
— Get current time (without date) in HH:MM:SS:mmm format:
SELECT CONVERT( CHAR(12), GetDate(), 14)
— Get current time (without date) in HH:MM:SS format:
SELECT CONVERT( CHAR(8), GetDate(), 14)
— Get current time (without date) in HH:MM format:
SELECT CONVERT( CHAR(6), GetDate(), 14)
Also see [story:t-sql-date-without-time]