SQL Server Date, Datetime and Datetime2 - What should I use when I get the value as a string?

When we have to store a datatime value (or date), in most cases, application accepts the value as a datetime and send the value to SQL Server as a datetime value. However, if the value is sent as a string (Example, CSV upload), then what should be the best way of formatting the value and how we can convert it to datatime without making any mistake?

It is always recommended to use ISO 8601 standard when exchanging datatime values. The standard describes the way of passing a datetime value, generally it is YYYY-MM-DDTHH:MM:SS.sss. You can read more on this my post: Best way to pass datetime values to SQL Server – SS SLUG Dec 2013 – Brain Bashers - Demo III.

With SQL Server 2016, the default string format for dates is YYYY-MM-DD. If you pass the value with this format, regardless of the Current Language set, SQL Server will accurately read the value. However, this does not work with all datetime data types as expected. Have a look on the following code;

SET LANGUAGE English;

DECLARE @Date date
DECLARE @Datetime datetime
DECLARE @Datetime2 datetime2

SET @Date = '2017-05-06';
SET @Datetime = '2017-05-06';
SET @Datetime2 = '2017-05-06';

SELECT DATENAME(mm, @Date) As WithEnglish;
SELECT DATENAME(mm, @Datetime) As WithEnglish;
SELECT DATENAME(mm, @Datetime2)As WithEnglish;
GO


SET LANGUAGE German;

DECLARE @Date date
DECLARE @Datetime datetime
DECLARE @Datetime2 datetime2

SET @Date = '2017-05-06';
SET @Datetime = '2017-05-06';
SET @Datetime2 = '2017-05-06';


SELECT DATENAME(mm, @Date) As WithGerman;
SELECT DATENAME(mm, @Datetime) As WithGerman;
SELECT DATENAME(mm, @Datetime2) As WithGerman;

As you see, Datetime data type convert happens based on the language set but Date and Datetime2 data types are accurately interpreted regardless of the language set. This is something you have to remember. If you expect datetime values as string and settings related to the session can be changed, then it is always better to use either Date or Datetime2.

If you need to make sure that date is properly getting interpreted regardless of the settings (language), then stick into ISO 8601. If you change the values of variable as below, you will get the same month: May for both English and German.

SET @Date = '2017-05-06T00:00:00';
SET @Datetime = '2017-05-06T00:00:00';
SET @Datetime2 = '2017-05-06T00:00:00';

Note that ISO 8601 accepts a value like 24:00:00 for time for midnight but SQL Server does not support it.

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.

What is the correct way for setting values for datetime?

How do we make sure that we accept values for datetime columns correctly? Has the month stored as it has been sent? Has the day stored as it has been sent? As long as you control the client application and SQL Server modules, you are sure about SQL Server’s identification of each components of datetime value. What if SQL Server accepts values from third-party application? Will they always send the datetime value as you asked?

Here is an example. The variable @datetime accepts a value. Assume that you accept datetime values in mmddyyyy format. If SQL Server works on default setting, it will recognize the moth of value as July with first setting. If you get it in different format, as showed in second setting, SQL Server will take some other part as the month and store, which is wrong in this scenario.

   1: DECLARE @datetime datetime

   2:  

   3: SET @datetime = '7/6/2010 2:30:00 pm'

   4: SELECT DATENAME(M, @datetime) AS MonthOfDate

   5:  

   6: SET @datetime = '6/7/2010 2:30:00 pm'

   7: SELECT DATENAME(M, @datetime) AS MonthOfDate

We can force SQL Server to always read the values as mmddyyyy by setting the DATEFORMAT. But, again, if client application sends it in different format, there is a chance of storing wrong values.

   1: SET DATEFORMAT mdy

   2: GO

   3: DECLARE @datetime datetime

   4:  

   5: SET @datetime = '7/6/2010 2:30:00 pm'

   6: SELECT DATENAME(M, @datetime) AS MonthOfDate

   7:  

   8: SET @datetime = '6/7/2010 2:30:00 pm'

   9: SELECT DATENAME(M, @datetime) AS MonthOfDate

What would be the best way? Why do not we stick with international standard? Yes, SQL Server accepts datetime values in ISO 8601 format. If accept values from third-party application, we can ask them to send the values in ISO 8601 format. The format of it is YYYY-MM-DDThh:mm:ss.

   1: DECLARE @datetime datetime

   2:  

   3: SET @datetime = '2010-07-06T14:30:00'

   4: SELECT DATENAME(M, @datetime) AS MonthOfDate

If SQL Server receives values in ISO 8601 format, it ignores all datetime related format such as DATEFORMAT, LANGUAGE and accepts the first part as the Year, second part as the Month, and third part as the Day. Note that date and time part has to be separated with “T” and time format should be in 24-hour format.