SQL Server Brain Basher of the Week #010 - Best way to send datetime value

Exchanging data between systems is not an uncommon scenario and it has become a part of most of the business. When exchanging data, the structure and format of the output is sometime determined before exchanging or sometime destination just accepts as it comes. Transferring date/time values is always getting involved with these exchanges and in a way, it is the most common attribute that we frequently troubleshoot as it introduces issues during the transfer, hence thought to add something on it as this week's Brain Basher.

What is the best of sending a datatime value to another system?

There are many ways of transferring datetime values. Transfer it as a string value, as a numeric value based on a predetermined format, or a string value based on a predetermined format are some common methods. However, though we try to manage it our best, still there is a chance for identifying the datetime value incorrectly when transferring from one system to another. What is the solution for this? What if we can use a format that can be recognized by any database management system?

There is a way of passing datatime values formatted with a standard that can be recognized by almost all DBMSs. It is called ISO 8601 standard. Almost all system support functionalities for retreiveing stored data/time values under this standard. Once the value is received by the destination, destination system can recognize the pattern without misinterpreting the value. This is how it has to be formatted;

Here is a simple code that shows how to convert a datetime value to ISO 8601 and how to accept an ISO 8601 formatted value.

-- Converting to ISO 8601
SELECT CONVERT(char(23), GETDATE(), 126);

-- Retrieving ISO 8601 formatted value
DECLARE @StringValue varchar(30) = '2015-05-03T10:50:00.000'

-- This returns "May" recognizing middle part as the month
SELECT DateName(m, Convert(datetime, @StringValue));

Once I discussed this with one of the sessions hosted at SS SLUG. Here is the post I made on it: http://dinesql.blogspot.com/2013/12/best-way-to-pass-datetime-values-to-sql.html.