Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2002
    Location
    Los Angeles
    Posts
    14

    Unanswered: 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.

    Joe

  2. #2
    Join Date
    May 2003
    Location
    UK
    Posts
    220
    Howdy,

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

  3. #3
    Join Date
    Mar 2002
    Location
    Los Angeles
    Posts
    14
    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

  4. #4
    Join Date
    Mar 2002
    Location
    Los Angeles
    Posts
    14
    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

  5. #5
    Join Date
    May 2003
    Location
    UK
    Posts
    220
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •