Hello, I am having trouble with my money data types. When my query produces results the money values are converted to a string data type; when exported to excel the results are exported to as text as well which makes the pivot tables function improperly. It doesn’t seem to be an issue with the INT data types.
See the attached screenshots.
Thanks
TDA datatype issue.doc (97.5 KB)
I tried this in TDA 2.6.1 and the money type is assigned to money column. It exports as general with no formatting.See screenshot. What version are you using?
I’m using 2.5.1
I am using money type but not your precision value. Can you send me your table ddl. Just copy from the script tab in database browser.
Also, what are your regional settings?
I do think we have an issue here I just need to replicate on my end.
Debbie
Unfortunately, I’m using a read only version and do not have the ability to provide you with the info you are requesting. Any other options? I working on getting upgraded to the newer version. Hopefully that will solve my problem.
From the Object Palette, right click and generate SQL | To Clipboard | Creation Script. That should also create the DDL. If not, I can work on this in the morning. Let me know.
Debbie
Sorry, I didn’t understand at first, As you probably guessed I’m new to TDA. Here is DDL for the table.
CREATE TABLE “MONTHEND201007”.dbo.LOAN(
CUSTOMERID char(10) ,
TYPE int ,
OPENDATE datetime ,
CLOSEDATE datetime ,
APPROVALCODE int ,
PAYMENT money(19, 4) ,
LASTPAYMENTDATE datetime ,
TERM int ,
DUEDATE datetime ,
INTERESTYTD money(19, 4) ,
INTERESTLASTYEAR money(19, 4) ,
INTERESTFROMOPEN money(19, 4) ,
INTERESTRATE decimal(6, 3) ,
BALANCE money(19, 4) ,
ORIGINALBALANCE money(19, 4) ,
CREDITLIMIT money(19, 4) ,
DESCRIPTION varchar(40) ,
MATURITYDATE datetime ,
AVAILABLECREDIT money(19, 4)
);
Okay. I think I see what is happening. You must be using ODBC to connect to SQL Server. There seems to be an error where we are giving percision to the money type. Money should never be represented as money(19,4).
You need to use the native SQL Server type. See screenshot. (Hope you are not using freeware as this is only available in commerical versions). If you are download a trial of TDA and it will work.
Also, in the current code base we are working in the export works ok with ODBC but the display is still wrong. I entered CR76,724 to fix the display and DDL generation.
Debbie
That was it. Thank you.
Good to hear.
