Chandana Chandana - 1 year ago 77
SQL Question

Formatting SQL Server date causes datatype issue

When I tried this SQL query

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

I get this result:

2016-07-01 23:19:16.573

But when I try this, it throws an 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 Source

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:

               STUFF('2016-06-28T23:44:23.7573244Z', 24, 4, ''),
               127), 'yyyy-MM-dd HH:mm')
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download