How to Fix ‘Conversion failed when converting date and time’ in SQL Server

When working with dates and times in SQL Server, it’s common for formatting discrepancies to cause issues during query execution. To prevent errors, it’s crucial to ensure that date strings are correctly formatted before any conversion takes place. The error “Conversion failed when converting date and/or time from character string” occurs when the date literal is not in a recognizable format, preventing SQL from interpreting it as a valid DateTime value.

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:

Declare @date_time_value varchar(100)= '10/16/2015 21:02:04'
select CONVERT(datetime2, @date_time_value, 103) as UK_Date_Time_Style

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.

Declare @date_time_value varchar(100)= '10/1/15 21:02:04'
select CONVERT(datetime2, @date_time_value, 103) as Date_Time_Style

Declare @date_time_value varchar(100)= '10/1/15 21:02:04'
select CONVERT(datetime2, @date_time_value, 3) as UK_Date_Time_Style

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.

Declare @date_time_value varchar(100)= '2019-02-29 21:02:04'
select cast(@date_time_value as datetime2) as date_time_value

Correct One:

Declare @date_time_value varchar(100)= '2019-02-28 21:02:04'
select cast(@date_time_value as datetime2) as date_time_value

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.

Declare @date_time_value varchar(100)= '2019-03-28 21:02:04'
select convert(datetime2,@date_time_value,126) as [yyyy-mm-ddThh:mi:ss.mmm]

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.