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.”

*
Figure 1: Date and/or time conversion error from the 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.

You are watching: Conversion failed when converting date and/or time from character string


Example 1:

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, 103) as UK_Date_Time_Style

*
Figure 2: Wrong Date Format resulting in an error

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, 103) as Date_Time_Style

*
Figure 3: Correct Date Format with “dd/mm/yyyy” British/ French date styleDeclare
date_time_value, 3) as UK_Date_Time_Style
*
Figure 4: Correct Date Format with “dd/mm/yy” British/ French date 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 as datetime2) as date_time_value

*
Figure 5: Error raised as 2019 is not a leap year, so it does not have 29 Feb as date

Correct One:

Declare
date_time_value as datetime2) as date_time_value
*
In UK style it is interpreted as “8th of March 2018”In European style it is interpreted as “3rd of August 2018”

Declare
date_time_value,126) as

*
Figure 7: International Standard ISO 8601 date format

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.


By Sadia MajeedSeptember 6, 2020
2 minutes read
*

Sadia MajeedDatabase Developer |
Sadia is an Enthusiastic and experienced professional with technical expertise in multiple tools and technologies including database, data ware housing, business intelligence and data science.

See more: Control Reaches End Of Non-Void Function C++, Solve Control Reaches End Of Non


About | Privacy Policy | Cookie Policy | Update Consent| Terms & Conditions | Editorial Guidelines | Affiliate Disclosure | Contact Us
Close
HOW-TOBUYER GUIDES

Expert Tip


How to Fix ‘Conversion Failed when Converting Date and/or Time from Character String’ Error?

If the issue is with your Computer or a Laptop you should try using Restoro which can scan the repositories and replace corrupt and missing files. This works in most cases, where the issue is originated due to a system corruption. You can download Restoro by clicking the Download button below.