How to Convert Date format as an ISO formate in SQL

Question

HI Team,

Currently we have Date Data set and need to Convert it to below mentioned format, Could you please Support to Convert into requirement format.

Original format - 2020-07-02 03:34:07.353

Current format after used Below Query- - 2020-07-02T03:34:07.353Z

select
SL_PostedTimeUtc as Original,
convert(varchar(32),SL_PostedTimeUtc,127)+'Z' as Converted
from StmALog

Required Format - 2020-07-02T03:34:00Z

Answer 1

Please try the following T-SQL:

DECLARE @var DATETIME = '2020-07-02 03:34:07.353';

SELECT @var AS [Before], FORMAT(@var, 'yyyy-MM-ddThh:mm:00Z') AS [After];

Output

Before                     After
2020-07-02 03:34:07.353 2020-07-02T03:34:00Z

Answer 2

Perhaps something like below:

convert(char(20), GETDATE(), 127) + '00Z'

Also, please don't post images and screen shots. We can't execute that code!

Answer 3

I'm confused. You already have an expression that is working for you, the one you posted. This gives you the format that you want.

Are you saying that you want some different, "better" expression? If so, please explain.

I assume that the column in question is a datetime data type, and please be aware that there is no such thing as an "original format". What you posted is how SSMS decides to format datetime values. SQL Server returns binary (unreadable) data and the client application does the formatting.