Input Date Formats

In this post, Data Education founder and trainer Adam Machanic discusses international standard date and time formats. Here, he explains the importance of not using ambiguous date formats.

There is really only one rule to remember when working with SQL Server’s date/time types from a client: Always, always, always, avoid ambiguous date formats! The unfortunate fact is that every date does not necessarily mean the same thing to any given two people. For instance, consider the following date:

01/02/03

If you’ve been living in the US, you probably immediately thought, “January 2, 2003!” However, those faring from European nations would read this as, “February 1, 2003.” And if you’re from one of various Asian countries (Japan, for instance), you probably think that this date signifies, “February 3, 2001.” Much like the inhabitants of these locales, SQL Server tries to follow the local format specifications when handling input date strings, meaning that on occasion users do not get the date they expect from the input.

Luckily, there is a solution to this problem. Just as with many other classes of problems in which lack of standardization is an issue, the International Standards Organization (ISO) has come to the rescue. ISO 8601 is an international standard date/time format, which SQL Server (and other software) will automatically detect and use, independent of the local server settings. The full ISO format is specified as follows:

yyyy-mm-ddThh:mi:ss.mmm

yyyy is the four-digit year, which is key to the format; any time SQL Server sees a four-digit year first, it knows that the ISO format is being used. mm and dd are month and day, respectively, and hh, mi, ss, and mmm are hours, minutes, seconds, and milliseconds. A few further notes on this format: The dashes and the T are both optional, but the T is only optional if the dashes are not present.

As with any other date/time format used by SQL Server, the time or date itself is always optional. If the time portion is not specified, SQL Server will use midnight as the default; if the date portion is not specified, SQL Server will use January 1st 1900 as the date.

Each of the following are valid ISO date/time strings:

--Date without dashes, and time
20060501 13:45:03

 --Date with dashes, and time specified with T
2006-05-01T13:45:03

--Date only
20060501

--Time only
13:45:03

Note that the following is valid according to the ISO format, but not treated as such by SQL Server:

--Date with dashes, and time--but no T
2006-05-01 13:45:03

By always using the ISO format—and always making sure that clients send dates according to the ISO format—you can ensure that the correct dates will always be used by SQL Server. Remember that SQL Server does not store the input date string; the date is converted into a binary format. So if invalid dates do end up in the database, there will be no way of reconstituting them from just the data.

Unfortunately, it’s not always possible to get data in exactly the right format before it hits the database. SQL Server provides two mechanisms that can help when dealing with nonstandard date/time formats: an extension to the CONVERT function that allows specification of a date “style” and a runtime setting called DATEFORMAT.

To use CONVERT to create an instance of DATETIME or SMALLDATETIME from a nonstandard date, use the third parameter to the function to specify the date’s format. The following code block shows how to do this for the “British/French” and “U.S.” styles:

--British/French style
SELECT CONVERT(DATETIME, '01/02/2003', 103)
--U.S. style
SELECT CONVERT(DATETIME, '01/02/2003', 101)

Style 103 produces the date “February 1, 2003,” whereas style 101 produces the date, “January 2, 2003.” By using these styles, you can more easily control how date/time input is processed, and explicitly tell SQL Server how to handle input strings. There are over 20 different styles documented; see the topic “CAST and CONVERT (Transact-SQL)” in SQL Server 2005 Books Online for a complete list.

The other option for controlling the format of input date strings is the DATEFORMAT setting. DATEFORMAT allows you to specify the input date format’s order of month, day, and year, using the specifiers M, D, and Y. The following T-SQL is equivalent to the previous example that used CONVERT:

--British/French style
SET DATEFORMAT DMY
SELECT CONVERT(DATETIME, '01/02/2003')

--U.S. style
SET DATEFORMAT MDY
SELECT CONVERT(DATETIME, '01/02/2003')

There is really not much of a difference between using DATEFORMAT and CONVERT to correct nonstandard inputs. DATEFORMAT may be cleaner in some cases as it only needs to be specified once per connection, but CONVERT offers slightly more control due to the number of styles that are available. In the end, you should choose whichever one makes the particular code you’re working on more easily readable, testable, and maintainable.