‘Conversion failed when converting date and time’ [Error Solved]

There are many instances when dates and times do not appear in the format you want it to be, nor does a query output fit the needs of the viewers. There are several SQL Server’s built-in features to format the date string according to your need but for the string to be interpreted by SQL Server and to avoid conversion errors it should be in a proper format. When we try to convert date or time from character string following error arises sometimes. “Conversion failed when converting date and/or time from character string.”

The error mentioned above normally arises when the date literal is not proper and cannot be converted from the string into DateTime or date. This error results due to a number of reasons, which we will discuss in detail along with the solution set.

Example 1:

United Kingdom Date and time notation display the date using the day-month-year format (January 10, 2015 or 10/1/2015) which we can achieve using SQL Server built_in feature “convert” function with formatting style 103.

Here in the example below we can see that the provided date string is in the wrong format. First, it is providing the month, then days and at last year which is wrong and cannot be interpreted by SQL Server which results 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 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 error, not because of the date or time formats used, rather it’s because you’re attempting to store incorrect information that’s not acceptable to the scheme.

Wrong Date:

The reason for the following error is mere 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 mass, it can be a usability issue to choose a datetime representation. So culture-specific date/time literals should be avoided. If we consider this date “03/08/2018”, it will be interpreted in following 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”

Luckily, there is one 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 it addresses all these issues. Whereas, “yyyy” is the year, “mm” is month and “dd” is day. So the date “8th of March 2018”in international ISO format is written as “2018-03-08”. Thus 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 assist to relieve the confusion I have frequently seen in the community about date/time values. However, it is recommended that never store dates in text-type (varchar, char, nvarchar, nchar,or text) Always store date value in DATE, DATETIME and preferably DATETIME2 (provides more precision) type columns and, leave the date information formatting to the user interface layer instead of being retrieved from the database.