December 26, 2018 at 8:54 am
Hi All,
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):
select DISTINCT
P.PolicyNumber
,S.RatingPeriod 'RatingPeriodNumber'
, 'VES' as 'RatingPeriodSource'
,P.Id 'PcmPolicyId'
,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'
December 27, 2018 at 9:03 am
what datatype is PolicyExpirationDate? Converting the null to datetime shouldn't be an issue. See below. Can you reproduce it in some sample data?
drop table if exists #t
go
Create table #T
(d1 datetime,
d2 datetimeOffset,
d3Null datetimeOffset)
insert into #t
values (getdate(),getdate(),null)
select *,
ISNULL(CONVERT(datetime,d3Null),'2030-12-31') AS PolicyExpirationDate
from #t
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 2, 2019 at 11:22 am
Thanks, @mike01. PolicyExpirationDate is of datatype datetimeoffset(7). I tried using the following for ActivationDate and it seems to have fixed the issue:
TRY_CONVERT(datetime,(select max(s2.EffectiveDate) from PolicyStatus S2 where s.PolicyId = s2.PolicyId and s.RatingPeriod = s2.RatingPeriod)) 'ActivationDate'
Thank you, all!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply