Datatypes and Decimals

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!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s