Conversion Failed When Converting Date and/or Time from Character String

This is a SQL Server error that occurs when calling CAST or CONVERT functions to extract data/time from a string value. The error means that SQL Server cannot extract date from text representation and/or store it into database due to incorrect format. This whitepaper explains most common reasons of the error and how to correct it.

The most common reasons of the error are:

• Non-existing date. Check that all numeric parts of the date (day, month, etc) belong to acceptable range: day cannot exceed 31/30/28 (depending on month) and month cannot exceed 12. For example, '2023-04-31' is not a correct date because April has 30 days.
• Misspelled month names, missing or extra characters. Check every single symbol of the date’s text representation
• Date format does not comply with SQL Server locale (see below)

Wrong Date Format

Wrong format of date is probably the most popular reason why SQL Server cannot extract date from its text representation. The reason of the problem is that different geographical regions write dates using a different format.

For example, query SELECT CONVERT(date, '11/15/2022', 103) generated the error "Conversion failed when converting date and/or time from character string" since the 3rd parameter specifies format DD/MM/YYYY and the given string is not complied with it.

How to Avoid the Error?

The best advice is to make sure the date string is compatible with the format. You can do this using function ISDATE that returns 1 for a valid date and 0 for an invalid. Output of the ISDATE function depends on SQL Server locale or date format settings. So, the method of validating date format consists of two steps:

• Set the required date format for the session by SET DATEFORMAT command
• Test the return value of ISDATE for the give string representation

For example, you need to check if @strdate contains a date representation complied with the format 103 (DD/MM/YYYY):

SET DATEFORMAT dmy  
IF ISDATE (@strdate) = 1  
    PRINT 'Valid Date'  
ELSE  
    PRINT 'Invalid Date';

Text to Date Best Practice

The following recommendations help you to convert strings to dates avoiding the error "Conversion failed when converting date and/or time from character string":

• Use the correct date format
• Update your regional settings or use ISO-8601
• Avoid culture-specific dates
• When it is possible, avoid storing dates as strings

You can update the regional settings via SET LANGUAGE statement that allows to specify locale that implicitly affects to date format. For example:

SET LANGUAGE us_english

Also, you can specify date format explicitly via SET DATEFORMAT statement. It accepts the following format values: mdy, dmy, ymd, ydm, myd, and dym:

SET DATEFORMAT dmy  

Another good practice of working with dates in SQL Server is to use ISO-8601 date format since is it locale independent. It has two variations for pure date and datetime that are YYYYMMDD and YYYY-MM-DDTHH:MM:SS. This means you don't have to use CAST or CONVERT functions to manipulate string representations of dates in ISO-8601 format:

CREATE TABLE tbl1(col1 datetime)
GO
INSERT INTO tbl1 VALUES ('2011-02-22T08:15:00')
GO

Have any questions? Contact us