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.

— Get current time (without date) in 24-hour time:
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]

Tags: SQL Server, T-SQL