Share via

How to Convert Date format as an ISO formate in SQL

Mahesh Madhusanka 216 Reputation points
2020-09-08T11:44:13.743+00:00

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
23245-image.png

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,865 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,640 questions
Accepted answer
  1. Yitzhak Khabinsky 25,866 Reputation points
    2020-09-08T13:55:26.6+00:00

    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

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. tibor_karaszi@hotmail.com 4,311 Reputation points
    2020-09-08T12:27:57.687+00:00

    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!

    1 person found this answer helpful.
  2. tibor_karaszi@hotmail.com 4,311 Reputation points
    2020-09-08T11:59:21.813+00:00

    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.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.