SQL Server query question - any way around this little issue?

[B@Th*nG]

when running the below query i'm getting the error "Conversion failed when converting date and/or time from character string.", but when you comment out the where clause it works fine. I know this is because of the order which sql analyses the code, but is there any way around this?

Many thanks,

B@

select * from 
(SELECT				P.PolicyId
                  , P.EffectiveDate
--Check of 'Original Policy Start Date' to identify if Outpatient paragraph should be applied or not
				  ,CASE
						WHEN CONVERT(CHAR(8), cast(pa.PolicyAttributeValue AS DATETIME), 112) < CONVERT(CHAR(8), CAST('2016-01-23' AS DATETIME), 112) THEN 'True'
						ELSE 'False'
					END  AS OutpatientParagraphs
FROM				Policy                   P
INNER JOIN          PolicyAttribute          pa   ON pa.PolicyId = p.PolicyId AND pa.EffectiveDate = p.EffectiveDate
AND					pa.PolicyAttributeId = (SELECT pad.PolicyAttributeID FROM PolicyAttributeDefintion pad WHERE pad.PolicyAttributeDesc = 'Original Policy Start Date') 
WHERE				P.PolicyStatus = 'RE' --and p.policyid not in (110228,130064)
AND					CONVERT( CHAR(8), P.EffectiveDate, 112) = '20160410' 
									) VW_RENEWAL_SCHEDULE
where OutpatientParagraphs = 'True'

 

[IntoTheVoid]

Try convert instead of cast:

select * from 
(SELECT				P.PolicyId
                  , P.EffectiveDate
--Check of 'Original Policy Start Date' to identify if Outpatient paragraph should be applied or not
				  ,CASE
						WHEN CONVERT(CHAR(8), CONVERT(Date, pa.PolicyAttributeValue, 112) < CONVERT(CHAR(8), CONVERT(Date,'2016-01-23', 112)) THEN 'True'
						ELSE 'False'
					END  AS OutpatientParagraphs
FROM				Policy                   P
INNER JOIN          PolicyAttribute          pa   ON pa.PolicyId = p.PolicyId AND pa.EffectiveDate = p.EffectiveDate
AND					pa.PolicyAttributeId = (SELECT pad.PolicyAttributeID FROM PolicyAttributeDefintion pad WHERE pad.PolicyAttributeDesc = 'Original Policy Start Date') 
WHERE				P.PolicyStatus = 'RE' --and p.policyid not in (110228,130064)
AND					CONVERT( CHAR(8), P.EffectiveDate, 112) = '20160410' 
									) VW_RENEWAL_SCHEDULE
where OutpatientParagraphs = 'True'

 

[fretted]

If you put the sub query in a CTE, then you will force the optimiser to evaluate the sub query separately to the main query and avoid this issue