The most common cause of this error is an incorrect format of the string being passed for conversion, such as using the wrong delimiter or an invalid date sequence. Improper formatting or special characters can also trigger this issue. Properly validating input and using consistent formats are essential steps to prevent conversion failures.
Example 1:
The United Kingdom’s date and time notation displays the date using the day-month-year format (e.g., January 10, 2015, or 10/1/2015), which we can achieve using SQL Server’s built-in “convert” function with formatting style 103. In the example below, we can see that the provided date string is in the wrong format. It first provides the month, then the day, and finally the year, which is incorrect and cannot be interpreted by SQL Server, resulting in an error. The correct format for UK-style date conversion using “103” date style is “dd/mm/yyyy.”
Wrong Format:
Correct Format:
The British and French date format is 103 = “dd/mm/yyyy” or 3 = “dd/mm/yy.” Here, 103 and 3 are the date styles.
Example 2:
Sometimes, string-to-date conversion in SQL Server results in an error, not because of the date or time formats used, but because you’re attempting to store incorrect information that’s not acceptable to the schema.
Wrong Date:
The reason for the following error is merely that in the year 2019, there is no such date as “29 February” because it is not a leap year.
Correct One:
ISO 8601 Date Format:
Although numerous formats are available for manipulating date values, when working for a global/international audience, it can be a usability issue to choose a datetime representation. Culture-specific date/time literals should be avoided. If we consider the date “03/08/2018,” it will be interpreted in different ways in different regions of the world.
In UK style, it is interpreted as “8th of March 2018.”In European style, it is interpreted as “3rd of August 2018.”
Fortunately, there is an alternative in the international date format developed by ISO. The global standard ISO 8601 format “YYYY-MM-DDThh:mm:ss” is a more language-independent option for string literals and addresses all these issues. Here, “yyyy” represents the year, “mm” the month, and “dd” the day. Thus, the date “8th of March 2018” in the international ISO format is written as “2018-03-08.” Therefore, the ISO format is the best choice for date representation.
Recommendations:
Hopefully, this article will help relieve the confusion frequently seen in the community about date/time values. However, it is recommended to never store dates in text-type (varchar, char, nvarchar, nchar, or text). Always store date values in DATE, DATETIME, and preferably DATETIME2 (which provides more precision) type columns, and leave the date information formatting to the user interface layer instead of being retrieved from the database.
