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

Dates and times in SQL Server are prone to errors in query execution because of date/time formatting inconsistency. Make sure that a date string is well formatted before its conversion, or such kind of mistake may occur. If the date literal is not in the proper format, SQL is not able to understand it as a DateTime value; hence, the "Conversion failed when converting date and/or time from character string".

Conversion failed when converting date and time in SQL Server

It happens because the string that one is converting has an improper format, including inappropriate delimiter and wrong date sequence. Another probable reason of this problem might be incorrect formatting or use of special characters. Proper validation of the input will also avoid any conversion mistake, along with using appropriate consistent formats.

One Example:

SQL Server provides support for the British date and time notation date format-day-month-year, like January 10, 2015 or 10/1/2015-by using its built-in "convert" function and with a formatting style of 103.

You can see, in this example, the incorrect format of the date string supplied. It has incorrectly supplied the month, day, and year in that order, which SQL Server does not understand and returns an error. The proper format for converting dates using the "103" date style to UK style is "dd/mm/yyyy."

Format error: inability to specify @date_time_value select CONVERT(datetime2, @date_time_value, 103), as UK_Date_Time_Style from the data set "varchar(100)= '10/16/2015 21:02:04'"Error Occurring Due to the Wrong Date Format Figure 2

Correct Format:

Three "dd/mm/yy" and 103 "dd/mm/yy" are the date format for British and French, respectively. Here, 103 and 3 are the date formats being used.

Declare variable '@date_time_value' with value '10/1/15 21:02:04' and use CONVERT function with the parameters 'datetime2' and '@date_time_value' as a value 103 as its source for the Date_Time_Style.Figure 3: The Correct Date Format using "dd/mm/yyyy" French and British datingDeclare @date_time_value as a varchar(100) and give it a value of '10/1/15 21:02:04'. Next, execute the CONVERT function against datetime2 and @date_time_value supplying 3 for UK_Date_Time_Style.Appropriate Date Format using "dd/mm/yy" Figure 4 French and British dating

The second sample is:

Sometimes the faulty data that you are trying to save can be the cause of date conversion failure in SQL Server - rather than the date or time formats applied.

Incorrect Date:

As the year 2019 is not a leap year, the error below occurs solely because the date "29 February" does not exist in that year.

Declare date and time. '2019-02-29 21:02:04' is the value of varchar(100). select the second datetime value from the parameter "cast" Figure 5: Because the year 2019 is not a leap year, 29th of February is not included. This is the reason for the occurring error.

Correct Option:

Declare the date and time value, please. '2019-02-28 21:02:04' is varchar(100) value. select the second datetime value from the parameter "cast"Picture 6: Updated listing

ISO 8601 Dates Standard:

While there are plenty of ways to manipulate date values, choosing a datetime representation may pose some usability issues when working with an audience based worldwide/international. Avoid using such date/time literals that are specific for any one culture. When we thought over the date "03/08/2018," people from different parts of the world will give it different meanings.

It is understood as "8th of March 2018." in British English.

That, in European writing, is "3rd of August 2018 ."

The good news is that ISO's international date format provides an alternate. Another, more language-neutral choice for string literals that takes care of all these problems is the "YYYY-MM-DDThh:mm:ss" format, which is defined by ISO 8601. In this context, the year is denoted by "yyyy", the month by "mm" and the day by "dd". Therefore, the international ISO format for the date "8th of March 2018" is "2018-03-08." Thus, when it comes to a presentation of dates, the ISO standard will be the most appropriate choice.

Declare @date_time_value varchar(100) = '2019-03-28 21:02:04', and by using the next code, datetime2 @date_time_value = 126, where mm is formatted date formatted as [yyyy-mm-ddThh:mi:ss.mmm].Date format according to International Standard ISO 8601 (Figure 7)

Comments for improvement:

It seems that when it comes to date/time values, the community usually gets confused, so hopefully, this article can clear it up for them. Datatypes like varchar, char, nvarchar, nchar, and text should never be used to hold dates. Never retrieve formatted date information from the database. Always store date values in columns of type DATE, DATETIME, or - when greater precision is required - DATETIME2.

You can resolve this problem or "Conversion error when converting data and time" to SQL Server by following the guide and troubleshooting the specific cause of the error.