I tried to replicate the issue, and got conversion failed error with the same code when there were trailing spaces in the varchar date column.
One solution that worked is to add TRIM() when referencing your varchar column CONVERT(DATE, (TRIM([CODE])+'01'))
Let me know if the issue still exists. If not accept the answer if it solves your issue :)
Conversion failed when converting date and/or time from character string
HI Team i have an issue with my query where the conversion issue. select * from Table where CONVERT(DATE,(CODE+'01')) >= GETDATE() getting this conversion issue Conversion failed when converting date and/or time from character string providing you the some sample data here
For selecting this query also getting the error, select CONVERT(DATE,(CODE+'01')) from Table Code 202411 202010 202009 202305 Code datatype is varchar. pleas help me to resolve this issue, any help or suggestion greatly helpful
3 answers
Sort by: Most helpful
-
Imaad Shaik 0 Reputation points
2024-01-17T14:51:40.66+00:00 -
Javier Villegas 900 Reputation points MVP
2024-01-17T16:52:14.0833333+00:00 if you are using SQL 2016 or older you have to use below CONVERT(DATE, (RTRIM(LTRIM([CODE]))+'01')) Regards Javier
-
LiHongMSFT-4306 30,666 Reputation points
2024-01-18T01:55:59.56+00:00 Hi @Naresh y I tested with this demo and get the desired output with no issue:
DECLARE @CODE TABLE (CODE VARCHAR(20)) INSERT INTO @CODE VALUES ('202411'),('202010'),('202009'),('202305') select CONVERT(DATE,(CODE+'01')) from @CODE
The problem may be that the values of the code column are not all strictly six-digit numbers. Please verify the length of code value with this query:
select *,len(CODE) from TABLE order by len(CODE) desc
Best regards, Cosmog Hong
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.