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?

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.

If the data can't be fixed, you can convert it to JSON by replacing $ with ":", \r\n with ":" and surround the result with {"and}"`:

declare @field nvarchar(3000)='EmployeeName$Test User
Email$test@test.com
ADUsername$tuser
TermDate$2023-07-13
EmployeeNumber$12345
Department$Tech'

declare @as_json varchar(3000)='{"' + replace(replace(@field,'$','":"'),CHAR(13)+CHAR(10),'","') + '"}'

The field is converted to JSON with this expression:

'{"' + replace(replace(@field,'$','":"'),CHAR(13)+CHAR(10),'","') + '"}'

You can extract the TermDate with a single call to JSON_VALUE and cast it directly to a date.

select cast(JSON_VALUE(@as_json,'$.TermDate') as date)

Or, as a single expression:

select cast(JSON_VALUE('{"' + replace(replace(@field,'$','":"'),CHAR(13)+CHAR(10),'","') + '"}','$.TermDate') as date)

If you have to do this frequently, you can even use the complete expression to create a computed column and even index it.

You can create a function to convert that format to JSON, to make it easier to write expressions:

create function To_Json(@field nvarchar(4000))
returns nvarchar(4000)
as
BEGIN
	return '{"' + replace(replace(@field,'$','":"'),CHAR(13)+CHAR(10),'","') + '"}'
END

And use it :

select cast(JSON_VALUE(dbo.To_Json(@field),'$.TermDate') as date)

You could also create a function that retrieves the path you want directly:

create function Get_By_Path(@field nvarchar(4000),@path nvarchar(200))
returns nvarchar(4000)
as
BEGIN
	declare @json nvarchar(4000)= '{"' + replace(replace(@field,'$','":"'),CHAR(13)+CHAR(10),'","') + '"}'
    return JSON_VALUE(@json,@path)
END

And use it in a query:

select cast(dbo.get_by_path(@field,'$.TermDate') as date)