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.