From:       To:      
Home > Documentation > SQL Server

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:

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:

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":

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