Every once in a while I have a numeric field that needs to have the digit or two after the decimal point, even if that number is zero. Last time, this was when I was documenting a software version in a numeric field, and today I ran into the same issue with an A1C value that I took from a “float” field and inserted into a varchar field. This created duplicate values which did not match. (8.0 from a competing source, 8 from this source.)
I’m pretty sure there’s a bunch of fancy stuff I don’t know yet with datatypes and how to format them, but for this one issue, I was able to look back into my email and find Joe Sweeney‘s very helpful response to my question last time:
“A numeric type is defined by numeric(p,s), where p is precision (how many digits total) and s is scale (how many digits after the decimal).”
I have read about scale and precision before, but it was so nice to have it stated so simply.
With this knowledge, I am attempting my very first column. Here goes:
ALTER TABLE DMProject.dbo.AllA1CRegLabResults
ALTER COLUMN HBA1C_LAST numeric(4,1);
Phew! It worked. My data didn’t disappear, and the whole numbers now have a “.0” after them. Hurray!
SQL Garden 