yyyy-MM-ddTHH:mm:ss.SSSSSSSZ format in sql server

Question

I have datetime in 9/9/2017 8:25:02 PM format and I need output in yyyy-MM-ddTHH:mm:ss.SSSSSSSZ format in SQL.

Also need the timezone as well like: -05:00 or +05:50

e.g. dateTime: 2016-02-02T12:44:18.7832703Z timezone: -06:00

`DATETIME` doesn't have a "format." Are you storing them as `VARCHAR`? Also, what timezone has `+05:50`...? — Siyual, Sep 19 '17 at 15:35
A quick Google search for "SQL Server format date" shows some options and examples for converting and formatting values when selecting from the data. What have you tried? — David, Sep 19 '17 at 15:37
@Siyual I don't know which one is +5:50 but there are some odd ones. Like Newfoundland that is -3:30. My guess is the OP mean :30 and typed :50 for the half hour. :) — Sean Lange, Sep 19 '17 at 15:58
How to convert 2/2/2017 12:44:18 PM into 2016-02-02T12:44:18.7832703Z — vkase, Sep 19 '17 at 17:20

score 0 · Answer 1 · edited Oct 28 '20 at 00:40

0

For the timezone you should use DateTimeOffset and not Datetime.

For the format of the string you have to use one of the predefined formats in SQL for example:

SELECT convert(varchar(50), dateoff (Column containing the DatetimeOffset), 127)

edited Oct 28 '20 at 00:40

Dale K

25,246
15
42
71

answered Sep 19 '17 at 15:54

Christian Jabr

39
3

What is dateoff? That is not a valid t-sql function. – Sean Lange Sep 19 '17 at 15:58
How to convert 2/2/2017 12:44:18 PM into 2016-02-02T12:44:18.7832703Z. – vkase Sep 19 '17 at 17:19

Maurício Pontalti Neri · Answer 2 · 2017-09-19T18:08:36.167

There is no way to convert SQL Server date format to Java date format

You can try Java to see Java format yyyy-MM-dd'T'HH:mm:ss.SSSz to yyyy-mm-dd HH:mm:ss

The closest in the SQL that is possible is using the TODATETIMEOFFSET, the problem of this function is that you need to know the time zone of the date

DECLARE @Timezone int = Datediff(MINUTE, GETUTCDATE(),getdate())
SELECT  CONVERT(datetimeoffset(0), TODATETIMEOFFSET (convert(datetime, '2/2/2017 12:44:18 PM'),@Timezone),127) [datetimeoffset(0)], Convert(varchar(50),  
TODATETIMEOFFSET (convert(datetime, '2/2/2017 12:44:18 PM'),@Timezone),127)[datetime]


datetimeoffset(0)                  datetime
---------------------------------- --------------------------------------------------
2017-02-02 12:44:18 -04:00         2017-02-02T16:44:18Z

TODATETIMEOFFSET (Transact-SQL)

AT TIME ZONE (Transact-SQL)

Date and Time Data Types and Functions (Transact-SQL)

How to convert 2/2/2017 12:44:18 PM into 2016-02-02T12:44:18.7832703Z — vkase, Sep 19 '17 at 17:20
There is no way to convert SQL Server date format to Java date format — Maurício Pontalti Neri, Sep 19 '17 at 18:08

score 0 · Answer 3 · answered Dec 07 '21 at 18:25

0

The answers here were right but they didn't give a clear answer to your exact case.

You could convert it this way:

DECLARE @Date DATETIME = CONVERT(DATETIMEOFFSET(4), N'2016-02-02T12:44:18.7832703Z');

yyyy-MM-ddTHH:mm:ss.SSSSSSSZ format in sql server

3 Answers3