I'm hoping to get guidance on this. I'm working on modifying a query used to pull data in an SSIS package. It consists of 3 unions, of which all the former subqueries work fine, however the last query is returning a "conversion of a datetimeoffset data type to a datetime data type resulted in an out-of-range value" error. I'm thinking it might be due to the PolicyExpirationDate field containing nulls, for policies which aren't expired yet. I tried applying what I did in the other subqueries of the union, but no luck. I haven't been able to find anything online that helps to correct this issue. Any advice would be greatly appreciated. Here is the subquery which is returning the error (along with my commented out attempts to fix the error):
, 'VES' as 'RatingPeriodSource'
,CONVERT(datetime,P.PolicyEffectiveDate) AS PolicyEffectiveDate
,CONVERT(datetime,P.PolicyExpirationDate) AS PolicyExpirationDate
--, ISNULL(CONVERT(datetime,P.PolicyExpirationDate),'2030-12-31') AS PolicyExpirationDate
--CONVERT(datetime, '2010-07-25T13:50:38.544', 126)
--,CONVERT(datetime, P.PolicyExpirationDate, 126)
, 'Coverage' as 'Level'
, pcsf.Name 'Factor_Name'
, pcsf.value 'Factor_Amount'
,(select max(s2.Id) from PolicyStatus S2 where s.PolicyId = s2.PolicyId and s.RatingPeriod = s2.RatingPeriod) 'PS_PolicyTransID'
,CONVERT(datetime,(select max(s2.EffectiveDate) from PolicyStatus S2 where s.PolicyId = s2.PolicyId and s.RatingPeriod = s2.RatingPeriod)) 'ActivationDate'
from policy p
inner JOIN PolicyStatus s ON s.PolicyId = P.Id
inner join policyschema ps on ps.policyid = p.id --get schema used on the policy
left outer join policyschemacoveragecontainer pscc on pscc.SchemaId = ps.id --get coverage level factors
left outer join policyschemacoverage psc on psc.containerid = pscc.id
left outer join policycoverageschemafactor pcsf on pcsf.coverageid = psc.id and pcsf.IsSelected = 1
where pcsf.Name not like 'Minimum Monthly Premium'