Convert from datetime to varchar to int

Question

Convert from datetime to varchar to int

daniness

SSCrazy

Points: 2890
More actions
January 8, 2018 at 8:54 am

#402810

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!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

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

ScottPletcher SSC Guru Points: 100940 More actions · Answer 2

, 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

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.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

Drop the "var", just use char(8).

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

daniness SSCrazy Points: 2890 More actions · Answer 4

John Mitchell-245523 - Monday, January 8, 2018 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

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: .

HappyGeek SSCoach Points: 18726 More actions · Answer 5

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

]

...

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

daniness - Monday, January 8, 2018 10:58 AM
John Mitchell-245523 - Monday, January 8, 2018 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
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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

HappyGeek - Monday, January 8, 2018 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
]

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

daniness SSCrazy Points: 2890 More actions · Answer 8

John Mitchell-245523 - Tuesday, January 9, 2018 1:46 AM
daniness - Monday, January 8, 2018 10:58 AM
John Mitchell-245523 - Monday, January 8, 2018 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
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.