date format query

Question

Dear Experts

Pleass help to conver the below date format to as mentioned.

SELECT FORMAT(DATEADD(dd, 0, '2021-04-02 18:54:13.4966667'), 'HHmmss')

Desired result is 185413

I am getting error like

conversion failed when converting date and/or time from character string.

Please help

Answer 1

You need to explicitly cast the string literal to datetime2, because the default conversion is to datetime, which only handles three decimals.

SELECT FORMAT(DATEADD(dd, 0, cast('2021-04-02 18:54:13.4966667' as datetime2(7))), 'HHmmss')