Converting Data type

KitaYama

Well-known member
Local time
Today, 15:48
Joined
Jan 6, 2022
Messages
2,144
I have a table in a sql server which is accessed by a Microsoft Access FE.
The mentioned table has a field named "Multiplier" with a "real" data type. It's used for some engineering calculations.
Because of a change in our company's policy and the problems with real data type, I'm considering changing the "real" data type to "money".
Will this convert effect on the first 4 decimal digit of my current data?

For now, I'm running the following query to see what will happen after the convert.
SQL:
SELECT
    Multiplier AS OriginalValue,
    CAST(Multiplier AS MONEY) AS AsMoney,
    Multiplier - CAST(Multiplier AS MONEY) AS Difference
FROM
    dbo.SingleEst
WHERE
    Multiplier IS NOT NULL
    AND ROUND(Multiplier, 4) = ROUND(CAST(Multiplier AS MONEY), 4)
    AND ROUND(Multiplier, 5) <> ROUND(CAST(Multiplier AS MONEY), 5);

The result after running the above query shows zero records.
But since the data is very crucial in my job, I thought I may ask for advices here before doing something that may cause me troubles in future.
I really don't want to run into a situation when several years down the road, when someone searches for an old record, the data on screen is different with the printed reports.

Thanks for any kind of advice.
 
"The result after running the above query shows zero records."

Is that desirable or undesirable? What is the significance of returning no records?
 
"The result after running the above query shows zero records."

Is that desirable or undesirable? What is the significance of returning no records?
That's desirable. (I assume.)
After converting data type from real to money, I don't care about the fifth decimal points onward. I need to have my data to be with the same integer and same first 4 digit after decimal point.

If my data in table is 12.0012345678, I need to have 12.0012 as my data. I don't care the remaining digits (345678) being disappeared.
The above query was just to test if after the convert, the data in the field is what I expect.

I wasn't sure if the query is correct, or I if using CAST to test in a query has the same effects of actually changing the data type of a field.
I wanted some expert like you or others, have a look at the query to see if I'm correct or not, and what you have to say on changing the data type of a table that's been in use for years.

Thanks.
 
Last edited:
I typically perform this type of analysis using Excel, as its flexibility and ease of data transformation and visualization make it very convenient. Since you're working with engineering data and large decimal values, it's a good idea to first create a backup of the database and modify the data type in that backup. Once that's set up, you can simulate the calculations in Excel using values that rely on the modified column to check for any breaking changes or unexpected behavior.

That's what I've done anyway, and it worked fine.
 
I wasn't sure if the query is correct
It's not.
By comparing the rounded values for equality of the first 4 digits, your criteria only shows values where there is no problem.
Instead you should check whether there are records where the first 4 digits are different.
 
It's not.
By comparing the rounded values for equality of the first 4 digits, your criteria only shows values where there is no problem.
Instead you should check whether there are records where the first 4 digits are different.
Thanks. I'm away from my PC. I'll check it as soons as I'm back.
I appreciate your input.
 

Users who are viewing this thread

Back
Top Bottom