Get HH:MM:SS from a datetime in MSSQL

Question

I have the following issue:

I have a datetime field, which contains entries like this: "1970-01-01 22:09:26.000"

I would like to extract only the 22:09:26 (hh:mm:ss) part, but I am unable to convert it into 24h format, I used FORMAT and CONVERT, but received the the am/pm culture (for the CONVERT I tried to use 13 culture value).

What is the simplest way to construct the formula to give back the above mentioned format?

Thank you!

Answer 1

1st way You can select the format you wish from https://www.mssqltips.com/sqlservertip/1145/date-and-time-conversions-using-sql-server/

select replace(convert(nvarchar(20), CAST('1970-01-01 22:09:26.000' AS datetime), 114),'-',':')

2nd way It is not a conversion,but if your entries are all the same format then you can use the below:

select right('1970-01-01 22:09:26.000',12)

Updated if you have null dates as well:

1.

select case when value is not null
        then replace(convert(nvarchar(20), CAST(value AS datetime), 114),'-',':')
            else null
       end

select case when value is not null then right(value,12)
     else null end