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.