Chandana Chandana - 5 months ago 13
SQL Question

formatting sql date gives datatype issue

When I tried the below sql query

select format(getdate(), 'yyyy-MM-dd HH:mm:ss.fff')


it gave me the below result.

2016-07-01 23:19:16.573


But when I try the below it throws error.

select format('2016-06-28T23:44:23.7573244Z', 'yyyy-MM-dd HH:mm')


Argument data type varchar is invalid for argument 1 of format function.

why is this happening. I think both are almost similar.

Answer

You need to first convert the string to a DATETIME object before you call FORMAT:

FORMAT(CONVERT(DATETIME, '2016-06-28T23:44:23.757Z', 127), 'yyyy-MM-dd HH:mm')

If you wanted to use your original input, you might have to STUFF away the extra time data beyond millisecond precision:

FORMAT(CONVERT(DATETIME,
               STUFF('2016-06-28T23:44:23.7573244Z', 24, 4, ''),
               127), 'yyyy-MM-dd HH:mm')