Convert Character string ISO date to DateTime and vice-versa

Here’s how to convert ISO8601 character data to DateTime and DateTime back to ISO8601 in SQL Server

DECLARE @IsoDate nvarchar(35)
SET @IsoDate = '2010-03-16T19:20:30.45123+01:00'

-- CONVERT TO DATETIME in SQL 2008
SELECT CAST(CONVERT(datetimeoffset, @IsoDate) AS datetime) as SQL2008

-- CONVERT TO DATETIME in SQL 2005 and earlier
SELECT CAST(LEFT(@IsoDate, 23) AS datetime) as SQL2005


-- CONVERT DateTime to ISO Date format
SELECT CONVERT(nvarchar(35), GETDATE(), 127) as ISO8601 ;

OUTPUT

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook