TSQL DATETIME ISO 8601

I have been given a specification that requires the ISO 8601 date format, does any one know the conversion codes or a way of getting these 2 examples:

ISO 8601 Extended Date 2000-01-14T13:42Z
ISO 8601 Basic Date 20090123T105321Z
151068 次浏览
小开
最佳答案

When dealing with dates in SQL Server, the ISO-8601 format is probably the best way to go, since it just works regardless of your language and culture settings.

In order to INSERT data into a SQL Server table, you don't need any conversion codes or anything at all - just specify your dates as literal strings

INSERT INTO MyTable(DateColumn) VALUES('20090430 12:34:56.790')

and you're done.

If you need to convert a date column to ISO-8601 format on SELECT, you can use conversion code 126 or 127 (with timezone information) to achieve the ISO format.

SELECT CONVERT(VARCHAR(33), DateColumn, 126) FROM MyTable

should give you:

2009-04-30T12:34:56.790
小开

This

SELECT CONVERT(NVARCHAR(30), GETDATE(), 126)

will produce this

2009-05-01T14:18:12.430

And some more detail on this can be found at MSDN.

小开

Gosh, NO!!! You're asking for a world of hurt if you store formatted dates in SQL Server. Always store your dates and times and one of the SQL Server "date/time" datatypes (DATETIME, DATE, TIME, DATETIME2, whatever). Let the front end code resolve the method of display and only store formatted dates when you're building a staging table to build a file from. If you absolutely must display ISO date/time formats from SQL Server, only do it at display time. I can't emphasize enough... do NOT store formatted dates/times in SQL Server.

{Edit}. The reasons for this are many but the most obvious are that, even with a nice ISO format (which is sortable), all future date calculations and searches (search for all rows in a given month, for example) will require at least an implicit conversion (which takes extra time) and if the stored formatted date isn't the format that you currently need, you'll need to first convert it to a date and then to the format you want.

The same holds true for front end code. If you store a formatted date (which is text), it requires the same gyrations to display the local date format defined either by windows or the app.

My recommendation is to always store the date/time as a DATETIME or other temporal datatype and only format the date at display time.

小开

If you just need to output the date in ISO8601 format including the trailing Z and you are on at least SQL Server 2012, then you may use FORMAT:

SELECT FORMAT(GetUtcDate(),'yyyy-MM-ddTHH:mm:ssZ')

This will give you something like:

2016-02-18T21:34:14Z

Just as @Pxtl points out in a comment FORMAT may have performance implications, a cost that has to be considered compared to any flexibility it brings.

小开

You technically have two options when speaking of ISO dates.

In general, if you're filtering specifically on Date values alone OR looking to persist date in a neutral fashion. Microsoft recommends using the language neutral format of ymd or y-m-d. Which are both valid ISO formats.

Note that the form '2007-02-12' is considered language-neutral only for the data types DATE, DATETIME2, and DATETIMEOFFSET.

Because of this, your safest bet is to persist/filter based on the always netural ymd format.

The code:

select convert(char(10), getdate(), 126) -- ISO YYYY-MM-DD
select convert(char(8), getdate(), 112) -- ISO YYYYMMDD (safest)
小开

For ISO 8601 format for Datetime & Datetime2, below is the recommendation from SQL Server. It does not support basic ISO 8601 format for datetime(yyyyMMddThhmmss).

DateTime

YYYY-MM-DDThh:mm:ss[.mmm]

YYYYMMDD[ hh:mm:ss[.mmm]]

Examples:

  1. 2004-05-23T14:25:10

  2. 2004-05-23T14:25:10.487

Datetime2

YYYY-MM-DDThh:mm:ss[.nnnnnnn]

YYYY-MM-DDThh:mm:ss[.nnnnnnn] Examples:

  1. 2004-05-23T14:25:10

  2. 2004-05-23T14:25:10.8849926

You can convert them using 126 option

--Datetime


DECLARE @table Table(ExtendedDate DATETIME, BasicDate Datetime)


DECLARE @ExtendedDate VARCHAR(30) = '2020-07-01T08:39:17' , @BasicDate VARCHAR(30) = '2009-01-23T10:53:21.000'


INSERT INTO @table(ExtendedDate, BasicDate)
SELECT convert(datetime,@ExtendedDate,126) ,convert(datetime,@BasicDate,126)


SELECT * FROM @table
go


-- Datetime2


DECLARE @table Table(ExtendedDate DATETIME2, BasicDate Datetime2)


DECLARE @ExtendedDate VARCHAR(30) = '2000-01-14T13:42:00.0000000' , @BasicDate VARCHAR(30) = '2009-01-23T10:53:21.0000000'


INSERT INTO @table(ExtendedDate, BasicDate)
SELECT convert(datetime2,@ExtendedDate,126) ,convert(datetime2,@BasicDate,126)


SELECT * FROM @table
go

Datetime

+-------------------------+-------------------------+
|      ExtendedDate       |        BasicDate        |
+-------------------------+-------------------------+
| 2020-07-01 08:39:17.000 | 2009-01-23 10:53:21.000 |
+-------------------------+-------------------------+

Datetime2


+-----------------------------+-----------------------------+
|        ExtendedDate         |          BasicDate          |
+-----------------------------+-----------------------------+
| 2000-01-14 13:42:00.0000000 | 2009-01-23 10:53:21.0000000 |
+-----------------------------+-----------------------------+
小开

this is very old question, but since I came here while searching worth putting my answer.

SELECT DATEPART(ISO_WEEK,'2020-11-13') AS ISO_8601_WeekNr

提交答案