January 8, 2018 at 8:54 am
Hi All,
So I've been working on an SSIS package and part of it involves pulling data from a table's datetime fields and inserting into another table's int fields. I'm having difficulty in converting the fields. Here's the code I've been trying (please ignore the commented out sections:
SELECT DISTINCT
c.sCustomerID
, p.sProductID
, c.sCustomerID + p.sProductID as CombinedClientProductID
, 'Unknown'
, po.sPolicyID AS PolicyAgreementNumber
, mv.[Market Vertical] as MarketVerticalName
--, cast(format(po.PolicyIssueDt, 'yyyymmdd') as int)
--, cast(format(po.PolicyEfftvDt, 'yyyymmdd') as int)
--, cast(format(po.PolicyExpirDt, 'yyyymmdd') as int)
--, cast(format(po.PolicyCancelDt,'yyyymmdd') as int)
--, convert(int, po.PolicyIssueDt, 112)
--, convert(int, po.PolicyEfftvDt, 112)
--, convert(int, po.PolicyExpirDt, 112)
--, convert(int, po.PolicyCancelDt, 112)
, convert (int, convert(varchar(8), po.PolicyIssueDt, 112)) as PolicyIssueDt
, convert((varchar(8),po.PolicyEfftvDt, 112) as int) as PolicyEfftvDt
, convert(varchar(8), po.PolicyExpirDt, 112) as int as PolicyExpirDt
, convert(varchar(8), po.PolicyCancelDt, 112) as int as PolicyCancelDt
, -1
, -1
FROM dbo.ExtPRSDEPolicy po
join dbo.ExtPRSDECustomer c on c.iCustomerKey = po.iCustomerKey
Join dbo.ExtPRSDEProduct p on p.iProductKey = po.iProductKey
LEFT JOIN ExtJWProductSegment jw ON p.sProductID = jw.sProductID
LEFT JOIN ExtMarketVertical mv ON LEFT(po.sPolicyID, 7) = mv.Policy_Number
WHERE jw.sProductID is not null OR p.sProductID = '9095'
Any feedback would be greatly appreciated. Thanks!
January 8, 2018 at 9:37 am
In those last three conversions you're doing, you're mixing the syntax for CAST (which uses "AS datatype") with the syntax for CONVERT (which uses a comma-separated list of parameters). Why not use the same syntax for those three conversions as you're using for the first (which works, certainly if PolicyIssueDt is datetime). Why do you need to do this, incidentally - why not just keep dates as date data types?
John
January 8, 2018 at 9:47 am
, cast (convert(varchar(8), po.PolicyIssueDt, 112) as int) as PolicyIssueDt
, cast(convert(varchar(8),po.PolicyEfftvDt, 112) as int) as PolicyEfftvDt
, cast(convert(varchar(8), po.PolicyExpirDt, 112) as int) as PolicyExpirDt
, cast(convert(varchar(8), po.PolicyCancelDt, 112) as int) as PolicyCancelDt
, -1
, -1
FROM dbo.ExtPRSDEPolicy po
join dbo.ExtPRSDECustomer c on c.iCustomerKey = po.iCustomerKey
Join dbo.ExtPRSDEProduct p on p.iProductKey = po.iProductKey
LEFT JOIN ExtJWProductSegment jw ON p.sProductID = jw.sProductID
LEFT JOIN ExtMarketVertical mv ON po.sPolicyID LIKE mv.Policy_Number + '%'
WHERE jw.sProductID is not null OR p.sProductID = '9095'
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 8, 2018 at 10:30 am
January 8, 2018 at 10:58 am
John Mitchell-245523 - Monday, January 8, 2018 9:37 AMIn those last three conversions you're doing, you're mixing the syntax for CAST (which uses "AS datatype") with the syntax for CONVERT (which uses a comma-separated list of parameters). Why not use the same syntax for those three conversions as you're using for the first (which works, certainly if PolicyIssueDt is datetime). Why do you need to do this, incidentally - why not just keep dates as date data types?John
Thanks, John. So you're saying I should go about the conversion as convert (int, convert(varchar(8), po.PolicyIssueDt, 112)) as PolicyIssueDt
?
To answer your q, the reason why it needs to be converted to an int is because the business would like to be compare this field with a an Accounting Period key in a view, which is an int datatype :doze: .
January 8, 2018 at 11:21 am
I may misunderstand the question and problem, forgive me if I do, but looking at AW, I would use something like the following to convert datetime to int:
select cast(SellStartDate as int) MyInt from Production.Product
select cast(ModifiedDate as int) MyInt from production.ProductInventory
]
...
January 9, 2018 at 1:46 am
daniness - Monday, January 8, 2018 10:58 AMJohn Mitchell-245523 - Monday, January 8, 2018 9:37 AMIn those last three conversions you're doing, you're mixing the syntax for CAST (which uses "AS datatype") with the syntax for CONVERT (which uses a comma-separated list of parameters). Why not use the same syntax for those three conversions as you're using for the first (which works, certainly if PolicyIssueDt is datetime). Why do you need to do this, incidentally - why not just keep dates as date data types?John
Thanks, John. So you're saying I should go about the conversion as
convert (int, convert(varchar(8), po.PolicyIssueDt, 112)) as PolicyIssueDt
?
To answer your q, the reason why it needs to be converted to an int is because the business would like to be compare this field with a an Accounting Period key in a view, which is an int datatype :doze: .
Yes. Test it, and if it gives the expected results, use it. Luis is right, though - use char instead of varchar.
John
January 9, 2018 at 1:53 am
HappyGeek - Monday, January 8, 2018 11:21 AMI may misunderstand the question and problem, forgive me if I do, but looking at AW, I would use something like the following to convert datetime to int:
select cast(SellStartDate as int) MyInt from Production.Product
select cast(ModifiedDate as int) MyInt from production.ProductInventory
]
I think the problem with that is that it converts the date into the number of days since 1900, not into an integer in the form yyyymmdd.
John
January 9, 2018 at 9:14 am
John Mitchell-245523 - Tuesday, January 9, 2018 1:46 AMdaniness - Monday, January 8, 2018 10:58 AMJohn Mitchell-245523 - Monday, January 8, 2018 9:37 AMIn those last three conversions you're doing, you're mixing the syntax for CAST (which uses "AS datatype") with the syntax for CONVERT (which uses a comma-separated list of parameters). Why not use the same syntax for those three conversions as you're using for the first (which works, certainly if PolicyIssueDt is datetime). Why do you need to do this, incidentally - why not just keep dates as date data types?John
Thanks, John. So you're saying I should go about the conversion as
convert (int, convert(varchar(8), po.PolicyIssueDt, 112)) as PolicyIssueDt
?
To answer your q, the reason why it needs to be converted to an int is because the business would like to be compare this field with a an Accounting Period key in a view, which is an int datatype :doze: .Yes. Test it, and if it gives the expected results, use it. Luis is right, though - use char instead of varchar.
John
Thank you! This worked :D.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply