Time part of a DateTime Field in SQL

This will return the time-Only

For SQL Server:

SELECT convert(varchar(8), getdate(), 108)

Explanation:

getDate() is giving current date and time.
108 is formatting/giving us the required portion i.e time in this case.
varchar(8) gives us the number of characters from that portion.
Like:
If you wrote varchar(7) there, it will give you 00:00:0
If you wrote varchar(6) there, it will give you 00:00:
If you wrote varchar(15) there, it will still give you 00:00:00 because it is giving output of just time portion.
SQLFiddle Demo

For MySQL:

SELECT DATE_FORMAT(NOW(), '%H:%i:%s')

SQLFiddle Demo