Tsql DATETIME ISO 8601

The necessary 'CONVERT'ion

In most cases, representing dates and times in SQL Server refers to the ISO 8601 format (either standard or extended), with the optional time zone:

• Standard: 'YYYYMMDDTHHMMSS' or 'YYYYMMDDTHHMMSSZ'
• Extended: 'YYYY-MM-DDTHH:MM:SS' or 'YYYY-MM-DDTHH:MM:SSZ' // very important for pretty-printing

TheCONVERT function, trusted sidekick of any SQL developer, is your go-to for this task. Use it with either style 126 (time) or style 127 (time zone):

SELECT CONVERT(NVARCHAR(30), GETDATE(), 126) AS ISO8601; 
-- Converts to Extended ISO 8601 format

SELECT CONVERT(NVARCHAR(30), GETDATE(), 127) AS ISO8601; 
-- Converts and includes the Zulu time (UTC+0)


Considering performance

Performance is a beast of burden when dealing with large data sets, and it's a 'no-no' to upset it. So, even though the FORMAT function may seem tempting, remember, it's available only from from T-SQL 2012 and later.

Instead, handle conversions elegantly using CONVERT with styles 126/127. Your performance metrics will thank you, and we may even throw in a medal 🏅! Here's a sneak peek at how to do it:

SELECT CONVERT(VARCHAR, GETDATE(), 127) as ISO8601; 
-- Nailed it, didn't we!?


Let's not forget our date(time) etiquette

Storing dates is an equivalent of your SQL high school dating scene — tricky and full of standards. Store them using SQL Server's DATETIME types, and only play the formatting game when you want to display them (dealing with angry breakups isn't fun, trust us!).

-- The classic 'I promise to change!' excuse... 
SELECT CONVERT(VARCHAR, your_datetime, 126) AS TimeOnly; 


Storing formatted dates is like double texting — it's uncool and impairs query performance, indexing, and calculations.

'YMD' — the universal language of love (or at least in TSQL)

Dating is all about communication, isn't it? Well, in SQL Server, it's YMD or YYYY-MM-DD that everyone understands, making it language-neutral for date insertion:

INSERT INTO your_table (date_column) VALUES ('20230316'); 
-- Texting in a language SQL Server can't resist


Who wants some timezone awareness?

Tired of timezone mix-ups ruining your data consistency? Enter DATETIMEOFFSET, the knight in shining armor, with your timezone offset right alongside your date and time data:

SELECT CONVERT(VARCHAR, your_datetimeoffset, 127) AS ISO8601; 
-- Unlike my ex, datetimeoffset cares about my timezone...
