TSQL DATETIME ISO 8601

Question

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

Solution 1

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

Solution 2

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.

Solution 3

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.

Solution 4

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.

Solution 5

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)

View more solutions

Comments

[Guillermo]

-1. You must insert them using this format: 2009-04-30T00:00:00 otherwise you may get a month vs day problem.

[marc_s]

@Guillermo: the format I use now (corrected it), 20090430 will also work on any SQL Server regardless of language and regional settings. Works great if you don't need / don't care about the time portion of your DATE, or if you're using the DATE datatype in SQL Server 2008....

[mschor]

I get a space where the "T" should be: 2004-12-14 10:05:59.000

[Steve Casey]

@Jeremy Ross - use CONVERT(nvarchar(30), DateField, 126). then you should get 2004-12-14T10:05:59.000

[artbristol]

126 appears to include timezone information if the field is a datetimeoffset. 127 converts it to UTC.

[David Burton]

I think you misunderstood - inserting '20121001' to a DateTime column will always convert the same regardless of regional settings - Marc wasn't suggesting the column type should be a string.

[Richard Ayotte]

Upvoted this answer because NVARCHAR doesn't return space padding.

[Phillip Senn]

I think CONVERT(varchar, DateField, 126) works as well.

[Phillip Senn]

You don't need to include the (30) part.

[blockloop]

-1 The OP was asking how to accomplish something and not for advice on whether or not it should be done. This is not an answer the original question, but an opinion based on little to no insight into why the user asked the question in the first place. The OP could very well have a hard requirement to do this and your response hasn't helped him at all. These kind of things should be posted as comments. Answers should always provide an answer.

[Jeff Moden]

@Blockloop, If someone asks how to shoot themselves in the head, you would be just as remiss not telling them it wasn't a good idea as not telling someone who asks how to do something wrong in SQL Server. Others in a hurry may not take the time to read mere comments. ;-)

[blockloop]

@JeffModen "Answer should always provide an answer" is quoted directly from the FAQ. Your reply did not answer the OP's question. If said metaphorical person was about to be burned alive then the shot in the head is the better choice. Context kills. ;-) If you must tell the OP why you believe his decision is bad, precede an answer with the reason.

[Jeff Moden]

@Blockloop, looking back at this, you're absolutely correct. I was taking it for granted that people would know the reasons why. Thank you for your good input. I've updated my response even though I'm a couple of years late.

[Admin]

@PhillipSenn I highly recommended leaving it in even if it's technically redundant here. nvarchar without an explicit size defaults to 30 in some contexts and to 1 in others, which hurts readability. Just try select convert(nvarchar, getdate(), 126); declare @v nvarchar = convert(nvarchar, getdate(), 126);: the first statement shows that this doesn't truncate. The second statement looks as if that value is stored in a variable of the exact same type. This is a very unintuitive aspect of SQL Server, I'd say.

[Admin]

@AaronBertrand I rolled back your edit. I agree with you about including a size, but using char is just a bad idea since contrary to what you wrote in the edit summary, format 126 doesn't always return the same length. I also don't understand why you would rename DateField to DateColumn in one place and leave it as DateField in another.

[Admin]

@marc_s By the way, with the data you included in here, you shouldn't be getting 2009-04-30T00:00:00.000, you should be getting 2009-04-30T00:00:00. The sub-second data is not output if there is no sub-second data. This may have been undocumented when you originally wrote your answer, I'm not sure, but it worked like that even back in SQL Server 2005.

[Adir D]

@hvd That might justify varchar vs. char (if that trailing space is ever going to be an issue; I doubt it); that doesn't mean you should leave off a length altogether. Please read this in full. This is about consistency and always using an explicit length, even when it doesn't seem to matter. As for DateField vs. DateColumn - because I'm human?

[Admin]

@AaronBertrand I did say I agreed with you about the length already. I commented about that on Cyberherbalist's answer before seeing your edit in here.

[Admin]

@AaronBertrand When using the rollback option, there's no way to do a partial rollback, and when I would undo more than I would redo, a rollback + subsequent edit seems more appropriate. I'll be happy to include the length again, or you can do it if you like, that part of your edit was a good improvement and it deserves your name on it in the next edit. As for an example, use the data in this post. select convert(varchar(23), cast('2009-04-30' as datetime), 126) returns 2009-04-30T00:00:00 (without any .000 at the end) of length 19.

[Adir D]

@hvd So why remove the length then? If you really wanted you could have improved my edit by using varchar(23) (the longest possible value using the old date/time data types) or varchar(33) (the longest possible value with the new types) and correcting the name I missed instead of just undoing it completely. Right now your reversion will actually silently truncate datetimeoffset values, because when you don't include a length, in this case, it'll truncate at 30 characters. At least with my edit there wouldn't have been any confusion as to why.

[Adir D]

@hvd So why do you care if the output of a varchar(23) only has 19 characters? I don't see the problem. Right now you have a varchar(30) and in that case the output is 19 characters. <shrug> I think you're picking nits and rolling back my edit was unnecessary. You could have saved all of this discussion by simply adding a comment (or by simply making an additional edit instead of rollback + new edit).

[Admin]

@AaronBertrand You made it char(23), not varchar(23), which made it return '2009-04-30T00:00:00 ' (the spaces get collapsed, but there were multiple). Those spaces can get it rejected as malformatted when it is subsequently parsed by client code.

[Adir D]

@hvd so you should have changed it to varchar(23), not varchar. I've changed it to varchar(33), hopefully that will address all of your fears. I don't know of a single client language that won't ignore trailing spaces when parsing a date; do you have an example of that?

[Adir D]

@RichardAyotte When will space padding ever matter? When would GETDATE() ever include Unicode characters (in other words, why nvarchar instead of varchar)?

[Admin]

@AaronBertrand Yes, that's probably better. As for client languages, I don't have a concrete example ready, although I have worked a lot in old Delphi versions where no ISO date parser was readily available. In such environments, hand coded parsers were the norm, and I'm sure you can imagine that not all hand coded parsers were tolerant.

[Admin]

@AaronBertrand By the way, it's okay to have missed something. Our conversation right now made it clear that you just weren't aware of it. You shouldn't see that as a waste of time, but even if you do, please next time show some more maturity in your edit summary.

[Adir D]

@hvd That I missed one instance of DateField? Or that I missed that you have some unfounded fear that every query will return data to some fictitious client language that blows up on trailing spaces? This is what I wasted my 15 minutes on, not learning from your graciousness that not all output will be 23 characters (and your rollback made that worse IMHO). Again, next time, just make another edit instead of rolling back and telling me about it, or leave me a comment and we can talk about it before stomping all over each other with a bunch of edits.

[Adir D]

@hvd And by the way, this kind of behavior is precisely why I stopped answering questions on Stack Overflow.

[Richard Ayotte]

@AaronBertrand varchar is better. I noticed that you updated the accepted answer so my vote and comment are outdated now.

[BradLaney]

There is no Z at the end.

[marc_s]

@BradLaney: yes - that's the way the style 126 works - it outputs a date/time string without timezone information (thus without a Z at the end). If you need timezone information, then you need to use style 127 instead. See CAST and CONVERT in T-SQL official documentation for more details ....

[BradLaney]

@marc_s 127 doesn't add the Z either.

[Fergus]

@Jeff Moden, I see your point of view. I also agree with your "answer".

[ideAvi]

this is cleaner and simpler, Thanks!

[Xin]

How to get million seconds SELECT FORMAT(GetUtcDate(),'yyyy-MM-ddTHH:mm:ss.SSSZ') does not work for SSS

[John P]

Does SELECT FORMAT(GetUtcDate(),'yyyy-MM-ddTHH:mm:ss.fffZ')work for you?

[Bacon Bits]

@AaronBertrand Because SSIS demands you translate the column to Unicode if your output is a varchar if you want to output to UTF-8 or UTF-16. It doesn't do that if you output an nvarchar.

[Adir D]

@BaconBits Are you saying SSIS won't accept a varchar date string? And that conversion won't happen implicitly when it has to? The question wasn't about an edge case with SSIS anyway.

[Adir D]

@ideAvi I agree that it is cleaner and simpler, but be aware that it can be problematic at scale.

[Bacon Bits]

@AaronBertrand No, it's not implicit. You get an error "cannot convert between unicode and non-unicode string data types" if you try implicit conversion. Even writing a varchar to a UTF-8 CSV file will error (through 2016 at least). You have to use a Derived Column Transformation or Data Conversion Transformation to convert from a DT_STR to a DT_WSTR or vice-versa. That's why I use nvarchar in the view when I need unicode output, and varchar when it has to be Windows-1252/ISO-8859-1. (And I know it's a corner case, but your question jumped out to me!)