[SOLVED] Query error with a date conversion on a string column

Query error with a date conversion on a string column

I have a SQL query, and I'm getting this error:

Msg 241, Level 16, State 1, Line 3
Conversion failed when converting date and/or time from character string.

Content is defined as a varchar(1024) column, and can have any value including date, but when I run it, I get error mentioned.

Any idea what is going on?

This is the query:

SELECT    
    Employer AS Employer, 
    CodeName AS CodeName, 
    Description AS Description, 
    StartDate AS StartDate, 
    EndDate AS EndDate, 
    Content AS Content, 
    Date AS Date
FROM  
    Table AS Result
WHERE
    CONVERT(datetime, Result.Content, 105) < CONVERT(datetime, '01-01-2025', 105))
ORDER BY 
    Employer, CodeName, Sequence, EndDate OPTION (maxdop 1)

Solution

Your column Content is of type varchar(1024) and you're trying to convert every value in it to a datetime. However, not every value in Content is a valid date string — it could contain random text which can't be converted.

Filter only those rows where Content is actually a valid date before trying to convert. SQL Server doesn’t have a built-in ISDATE with format support but you can use TRY_CONVERT or TRY_CAST (if you are using SQL Server 2012+) which will return NULL when conversion fails instead of raising an error.

Try:

SELECT    
    Employer AS Employer, 
    CodeName AS CodeName, 
    Description AS Description, 
    StartDate AS StartDate, 
    EndDate AS EndDate, 
    Content AS Content, 
    Date AS Date
FROM  Table AS Result
WHERE TRY_CONVERT(datetime, Result.Content, 105) < CONVERT(datetime, '01-01-2025', 105)
ORDER BY Employer, CodeName, Sequence, EndDate OPTION (maxdop 1);