Unanswered: How to Change Precision and Scale in MS SQL Server 2000
My table was created by importing from an Excel spreadsheet. Typical fields in the rows are a date and various stock values like Open, High, Low, and Close. Unfortunately, many of the values have the wrong characteristics. These are my problems:
1. The date field has time in addition to the date. I don't want the
time in this field.
2. Many of the amount fields have a large precision, for example,
1.9399999999999999. I want to allow for a precision of 5 and a
scale of 2.
Can I make changes to my table at this point? I looked into Design Table but I don't see any feature allowing me to make changes 'on the fly'.
Usually you can query a datetime column to extract just the date, so dont worry too much about that.
The column precision can be changed ( on the fly as it were ) using an alter table command ( see BOL ) that will automatically change the column precision and in the process round the values to what you want.
( PS - theres nothing quite like a V8 Holden ute......)
Thank you for the reply. It occurs to me that one can end up creating a great many different queries if one is interested in comparing possible results/outputs. If DBA's want to save their queries for possible use later do they typically like to store them in a standard folder? Or should one create a special folder within the 'Databases' folder?
Thanks again. I am still new to working with Query Analyzer.
Well, sadly SQL doesnt handle splitting out dates from datetime fields very well.
Assuming you had a column called DATE in a table called INFO, if you want to display JUST the date, you need to extract the hour, min, seconds as characher values then reconstruct into a character format ( and later change to datetime , which by the way gives a defualt date of 01/01/1900)
Now, assuming you have a small table called INFO, with one column called DATE with one value of 2003-10-10 17:23:34
If you xxtract using time the following code -
This gives -
17:23:34 ( but in varchar format).
Note too that single digit values WILL NOT have a '0' put in front of them unless you test for it & code for it accordingly.
Unless you get all dates into the same format - e.g. all varchar/char or all in datetime, mixing & matching will give you a headache.