Conversion failed when converting SMALLDATETIME values stored as VARCHAR to DATE.

Question

Hi,

I have a table with millions of records and one of the columns (Hit_DateTime) is set as VARCHAR datatype but the values the column hold are all in SMALLDATETIME (Sample 2023-04-26 20:57:00), I'm trying to split the date part alone from this column and doing an INSERT into another table using SELECT CAST(SUBSTRING(Hit_DateTime,0,10) As DATE) but for some reason it keeps failing for the error -> Conversion failed when converting date and/or time from character string.

I have tried multiple ways to fix this issue but unfortunately, nothing works. And also I can't find any issues with the data I have got. PFA the distinct values I currently have in my table for the mentioned column.

Results.txt

Please help me here.

Answer 1

Hello @Vivek Komarla Bhaskar ,

Thanks for the question and using MS Q&A platform.

I tried this

Create table sinkasVarchar

(

Hit_DateTime varchar(100)

)

INSERT into sinkasVarchar(Hit_DateTime) values ('2022-12-01 00:04:00')

You are doing this
SELECT CAST(SUBSTRING(Hit_DateTime,0,10)As DATE)

And getting the below error

Msg 241, Level 16, State 1, Line 26

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

Pl;ease do and this and this will solve the issue

SELECT CAST(SUBSTRING(Hit_DateTime,0,11)As DATE) from sinkasVarchar

Let me know how it goes .

Thanks

Himanshu

Please accept as "Yes" if the answer provided is useful , so that you can help others in the community looking for remediation for similar issues.