SQL Convert Varchar to Date

I have a query that grabs a yyyy-MM-dd format from a large storage field by substringing, so the value is automatically a varchar. An example of the entire record’s contents is below:

EmployeeName$Test User
Email$test@test.com
ADUsername$tuser
TermDate$2023-07-13
EmployeeNumber$12345
Department$Tech

The following SQL query works and returns all data correctly, where TermDateString is never null and appears to be always formatted correctly:

with cte as (
    select i.IncidentNumber,
    case when charindex('TermDate$',i.symptom) = 0 or charindex('EmployeeNumber$',i.symptom) = 0 then null else
    substring(symptom,charindex('TermDate$',i.symptom) + 9,(charindex('EmployeeNumber$',i.symptom) - 2) - (charindex('TermDate$',i.symptom) + 9)) 
    end as 'TermDateString'
    from Incident i
    inner join task t on t.parentlink_recid = i.recid
    where i.subject like '%term in proc%'
    and i.status not in ('closed','cancelled','resolved')
    and i.symptom like '%EmployeeName%'
    and t.subject = 'Open WFH Equipment Return Request'
    and t.status not in ('closed','cancelled')
    --and i.incidentnumber = '5305093'
)
select *
--,convert(date,TermDateString,23) 'TermDate'
--,cast(TermDateString as date) 'TermDate'
from cte
--where convert(date,TermDateString,23) > DATEADD(month, -4, GETDATE())
order by incidentnumber desc

However, I need to only get tickets where TermDateString is within a certain time frame. When converting the varchar to a date or datetime, by uncommenting --,convert(date,TermDateString,23) 'TermDate', it always throws this error:
Conversion failed when converting date and/or time from character string.

I believe that it is not related to a specific record’s data, as it is never null and I checked there are no leading/trailing white spaces. Additionally, I manually tested multiple records by uncommenting --and i.incidentnumber = '5305093', which is the top result, as well as the 2nd result. Individually, both of those worked. However when doing a Select top 2 i.incidentnumber with --and i.incidentnumber = 'xxxx' still commented out, it throws the same error, meaning it only throws an error when it is trying to handle multiple records. I have also tried using CAST instead of CONVERT, but with the exact same results.

Does anybody know of a way to get this working?

>Solution :

I think that The error "Conversion failed when converting date and/or time from character string" occurs when there are invalid date values in the TermDateString column. To resolve this issue and retrieve tickets within a specific time frame, you can modify your query as follows:

sql

WITH cte AS (
    SELECT i.IncidentNumber,
    CASE WHEN CHARINDEX('TermDate$', i.symptom) = 0 OR CHARINDEX('EmployeeNumber$', i.symptom) = 0 THEN NULL ELSE
    SUBSTRING(symptom, CHARINDEX('TermDate$', i.symptom) + 9, (CHARINDEX('EmployeeNumber$', i.symptom) - 2) - (CHARINDEX('TermDate$', i.symptom) + 9)) 
    END AS 'TermDateString'
    FROM Incident i
    INNER JOIN task t ON t.parentlink_recid = i.recid
    WHERE i.subject LIKE '%term in proc%'
    AND i.status NOT IN ('closed', 'cancelled', 'resolved')
    AND i.symptom LIKE '%EmployeeName%'
    AND t.subject = 'Open WFH Equipment Return Request'
    AND t.status NOT IN ('closed', 'cancelled')
)
SELECT *
FROM cte
WHERE TRY_CONVERT(date, TermDateString) BETWEEN '2021-01-01' AND '2021-12-31'
ORDER BY IncidentNumber DESC;
```

In this modified version, I replaced the conversion function with TRY_CONVERT(date, TermDateString), which gracefully handles any invalid date values and returns NULL instead of throwing an error. Additionally, I added a BETWEEN condition to filter the TermDateString values within your desired time frame (in this case, from January 1, 2021, to December 31, 2021). You can adjust the date range according to your requirements.

By using TRY_CONVERT and the BETWEEN condition, the query should execute successfully, retrieving the tickets within the specified time frame without encountering any conversion errors.