Datetime compare query only works when particular rows are specified

Transact-SQL
1

The following query results in a "Conversion failed when converting data and/or time from character string" as soon as more than 1 id is selected. If "and id in ('5','6')" is added to the end, the query will provide the ids. If omitted and the same top 2 ids are selected, the conversion fails. This enrollmentdate is stored by the software as an nvarchar - format MMDDYYYY.

declare @yesterday datetime
set @yesterday=cast(DATEAdd(DAY,-1,getdate()) as DATE)

select id from customers
where cast(isnull(
substring(Enrollmentdate,5,4)+'-'+
substring(EnrollmentDate,1,2)+'-'+
substring(EnrollmentDate,3,2),'1990-01-01')as DATE) > @yesterday
--and id in ('5','6')

2

This is likely because there are some rows in which the Enrollment date is not in the form you are expecting. You can test if that is the case by running the following query:

SELECT id,
	Enrollmentdate
FROM
	customers
WHERE
	TRY_PARSE
	(
		SUBSTRING(Enrollmentdate, 5, 4) + 
		SUBSTRING(Enrollmentdate, 1, 2) + 
		SUBSTRING(Enrollmentdate, 3, 2) 
	) IS NULL;

For Id = 5 and 6, the corresponding Enrollmentdate values are in the expected format. That is why your query succeeds when you add the id filter to the where clause

EDIT: TRY_PARSE is a feature available in SQL 2012 or later. If you are on an older version, use this instead:

SELECT id,
	Enrollmentdate
FROM
	customers
WHERE
	ISDATE (  
	SUBSTRING(Enrollmentdate, 5, 4) + 
	SUBSTRING(Enrollmentdate, 1, 2) + 
	SUBSTRING(Enrollmentdate, 3, 2) 
	) = 0;
1 Like