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

Naresh y 146 Reputation points
2024-01-17T13:38:44.7333333+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,470 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Imaad Shaik 0 Reputation points
    2024-01-17T14:51:40.66+00:00

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

    0 comments No comments

  2. 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

    0 comments No comments

  3. 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.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.