Getting Conversion failed error when converting date and/or time from character string

0
votes

In my table values are saved as Thursday November 12, 2020 07:01. I want to convert those values to the date time. This is the query which I wrote.

select  
 convert(varchar(200), replace(replace(replace(replace(replace(replace(replace(publishedon,'Monday',''),'Saturday',''),'Sunday',''),'Tuesday',''),'Wednesday',''),'Thursday',''),'Friday','')) 
from py_EconNext

Once I removed the days from the values, I can get values as November 12, 2020 11:04. After that I try to convert it to datetime. When converting this to the datetime, I’m getting the error

Conversion failed when converting date and/or time from character string.

But when I run select as below

select  convert(datetime,convert(varchar(200),(' November 12, 2020  11:04')))

I can get the values as 2020-11-12 11:04:00.000 without any error.

Table column datatype - varchar(200)

3
sqlsql-servertsql
You fell victim to one of the classic blunders - the most famous of which is "never get involved in a land war in Asia" - but only slightly less well-known is this: "Never put dates in a column with varchar as the type!" - Joel Coehoorn
@JoelCoehoorn You just love that quote and movie! - SMor
@SMor I do. :) It's also easier to copy/paste the same thing each time, and humor helps soften the blow when you need to tell someone they did it wrong. - Joel Coehoorn

3 Answers

1
votes

Storing datetime in varchar column is bad idea.Use appropriate column type when storing data.

Considering your question i assume there is invalid characters included in data like CR, LF or CR+LF.

try to replace them by

SELECT REPLACE(REPLACE(@str, CHAR(13), ''), CHAR(10), '')
2
votes

The problem is that you have inconsistent styles of a date, that is (foolishly) being stored as a varchar. This is why storing dates as a varchar is always a bad idea. The real solution is fix your design.

You can get around this with a couple of TRY_CONVERTs, provided that your style is always mon dd, yyyy hh:mi:ss or day, mon dd, yyyy hh:mi:ss. I am using the style code 109 to be explicit on the style code mon dd yyyy hh:mi:ss:mmmAM (or PM) (note that it is happy to accept a 24 clock too):

SELECT ISNULL(TRY_CONVERT(datetime,V.YourString,109),TRY_CONVERT(datetime,STUFF(V.YourString,1,CHARINDEX(' ',V.YourString),''),109))
FROM (VALUES('Thursday November 12, 2020 17:01'),('Thursday November 12, 2020 07:01'),('November 12, 2020 07:01'))V(YourString)
2
votes

Rather than converting into a VARCHAR (which it already is), it would make more sense to convert straight into a DATETIME.

As well as that, there is no need to have all the embedded REPLACE statements - just take all of the text from the string after the first space.

Try this code:

CREATE TABLE #py_EconNext (publishedon VARCHAR(200));
INSERT INTO #py_econNext VALUES ('Thursday November 12, 2020 07:01');

select convert(datetime, RIGHT(publishedon, LEN(publishedon) - CHARINDEX(' ', publishedon))) 
 from #py_EconNext;

DROP TABLE #py_EconNext;

Output:

2020-11-12 07:01:00.000

Edit following suggestion to use TRY_CONVERT

Following a helpful comment suggesting the use of TRY_CONVERT in place of CONVERT, please see the equivalent code using the other function:

CREATE TABLE #py_EconNext (publishedon VARCHAR(200));
INSERT INTO #py_econNext VALUES ('Thursday November 12, 2020 07:01'),('Not valid');

select try_convert(datetime, RIGHT(publishedon, LEN(publishedon) - CHARINDEX(' ', publishedon))) 
 from #py_EconNext 

DROP TABLE #py_EconNext;

Output:

2020-11-12 07:01:00.000
NULL

This benefits from not causing an error if the conversion fails, but rather returns NULL.

My personal view is that a data conversion failure is an error and should be treated as such, but the desired solution would ultimately be dictated by the use case OP is outlining.