How to Change Precision and Scale in MS SQL Server 2000

Hello,

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'.

Any suggestions are welcome.

JoeHowdy,

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.

Cheers,

SG
( 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.

Joe|||I would also like to ask anyone if he or she could advise me as to how I can display only a date (without the time) when I do a query using Query Analyzer. I really don't want to see time displayed.

Can someone assist?

Thanks again.

Joe|||Howdy,

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 -

select convert(varchar(2),datepart(hh,DATE))
+':'+convert(varchar(2),datepart(mm,DATE))
+':'+convert(varchar(2),datepart(ss,DATE))
from INFO

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.

Cheers,

SG.