Cast and substring the Date time column in SQL Server

Question

I have a column OFFSTARTIME with a datetime datatype. Currently data is appearing as

1900-01-01 08:00:00.000

I want to show only

08:00:00

For that I have tried the following query:

SUBSTRING(CAST(OFFSTARTIME AS VARCHAR(19)), 12, 19)

but it's showing an output of

0 8:00AM

Answer 1

There is datatype for time only, it's time datatype :)

Try cast(offstarttime as time).

If you really want to cast, then cast it to varchar and use right function: right(cast(offstarttime as varchar(23)), 12)

Answer 2

You have to convert value to varchar to get requested output as below. check other formats as well

DECLARE @myTime Datetime='1900-01-01 08:00:00.000'

SELECT CONVERT(VARCHAR(8), @myTime, 108)