question

antonyjoseph-5093 avatar image
0 Votes"
antonyjoseph-5093 asked ErlandSommarskog answered

date format query

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

sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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')
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.