Post reply

time field

  • February 5, 2008 at 7:36 am

    #178881

    I have a time field where it returns time in milli seconds like

    23.00001

    12.9

    16

    14.567

    How can i restrict this field so tht it returns with only onw decimal point.

  • February 5, 2008 at 7:38 am

    #775123

    you need to cast or convert the value that's in the field as a decimal.

    declaration is size,number of decimals:

    so SELECT CONVERT(DECIMAL(8,1),YOURCOLUMN FROM YOURTABLE wouold do what you ask.

    example:

    SELECT CONVERT(DECIMAL(8,1),23.00001)

    ,CONVERT(DECIMAL(8,1),12.9)

    , CONVERT(DECIMAL(8,1),16)

    ,CONVERT(DECIMAL(8,1),14.567)

    Results:

    (No column name)(No column name)(No column name)(No column name)

    23.012.916.014.6

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 5, 2008 at 8:02 am

    #775137

    when am doing this

    sum(convert(decimal(8,1),t.duration)/360000) as Duration

    am getting

    51.00000000

    12.40000000

    30.80000000

    15.00000000

    48.10000000

    1.60000000

    but i need only 1 decimal point

  • February 5, 2008 at 8:15 am

    #775141

    Mike Levan (2/5/2008)

    when am doing this

    sum(convert(decimal(8,1),t.duration)/360000) as Duration

    am getting

    51.00000000

    12.40000000

    30.80000000

    15.00000000

    48.10000000

    1.60000000

    but i need only 1 decimal point

    you divided the converted value by 360000.

    you want the final result to be a decimal.

    try

    this:

    sum(convert(decimal(8,1),(t.duration)/360000)) as Duration

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 5, 2008 at 8:18 am

    #775145

    I tried it already but the values i got are different

    15.0

    6.0

    7.0

    6.0

    39.0

    0.0

    I want to divide duration/360000 and the value returned shud have one decimal point

  • February 5, 2008 at 9:39 am

    #775203

    I want to divide duration/360000 and the value returned shud have only one decimal point .

    If the value returned is 51.000001, i need just 51.0

  • February 5, 2008 at 9:53 am

    #775210

    you need to post more specific details. the code i posted will do what you asked; so you are most likely not converting the final answer.

    here's a more detailed example:

    declare @tbl TABLE(tblId int identity(1,1) NOT NULL PRIMARY KEY, SOMEVAL FLOAT )

    INSERT INTO @tbl(SOMEVAL)

    SELECT 23.00001 * 360000 UNION

    SELECT 12.39 * 360000 UNION

    SELECT 16.11 * 360000 UNION

    SELECT 14.567 * 360000

    SELECT * FROM @tbl

    SELECT sum(SOMEVAL/360000) as BadDuration, sum(convert(decimal(8,1),(SOMEVAL)/360000)) as Duration

    from @tbl

    group by tblid

    BadDuration Duration

    ---------------------- ---------------------------------------

    12.39 12.4

    14.567 14.6

    16.11 16.1

    23.00001 23.0

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 5, 2008 at 10:08 am

    #775220

    sum(cast(t.duration as float(5,1))/360000) as Duration ,result is 12.41

    sum(convert(decimal(8,1),(t.duration)/360000)) as Duration, result is 6.0

    12.41 is the correct value. but i just need to display only 12.4.

    according to the second conversion the values are wrong

  • February 5, 2008 at 11:09 am

    #775254

    Float and Decimal math are two entirely different "animals". From what you seem to be talking about - you're going to want to steer towards decimal types, which are precise.

    I'm pretty sure you don't even want to be storing them as float either, since that's going to get you into the float point arithmetics, precision and scale game.

    Rounding is essentially something that doesn't have a whole lot of significance, and certainly not the SAME significance in float-point math vs decimal-point math.

    After all -

    use testing

    declare @t float

    declare @U float

    select @t=12.39999999999999999

    select @U=12.40000000000000000

    select case when @t=@u then 'equal' else 'not equal' end

    go

    declare @t decimal(25,20)

    declare @U decimal(25,20)

    select @t=12.39999999999999999

    select @U=12.40000000000000000

    select case when @t=@u then 'equal' else 'not equal' end

    Results: (float) EQUAL, (Decimal) NOT EQUAL

    And remember - the two operations aren't transitive, so

    Sum(CAST(value as decimal(8,1)) is NOT the same as CAST(sum(value) as Decimal(8,1)) with float values.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • February 5, 2008 at 3:59 pm

    #775367

    I got this example from Sergiy... it totally changed my mind about the use of Float. Pay particular attention to the difference between the 3rd and 4th column in the results... come to your own conclusion...

    DECLARE @F FLOAT

    SET @F = .3333

    DECLARE @D DECIMAL(4,2)

    SET @D = .3333 --Simulates precision/scale lost during a calculation

    SELECT N,

    [N*@F] = N*@F,

    [N*@D] = N*@D,

    [STR(N*@F,4,2)] = STR(N*@F,4,2),

    [ROUND(N*@F,2)] = ROUND(N*@F,2),

    [CONVERT(DECIMAL(4,2),N*@F)] = CONVERT(DECIMAL(4,2),N*@F)

    FROM dbo.Tally

    WHERE N <= 10

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 6, 2008 at 1:20 am

    #775457

    Don't be shy Jeff.

    Why float is inprecise is discussed here and also explained why.

    http://www.sqlservercentral.com/Forums/Topic356756-8-1.aspx

    The basic reason is that it is not possible with binary representation to get exactly all possible decimal values with IEEE754.

    Integers are ok, but not decimals.

    At least not with base10 representation 🙂

    N 56°04'39.16"
    E 12°55'05.25"

  • February 6, 2008 at 2:40 am

    #775486

    What is dbo.Tally for this example?

    James Horsley
    Workflow Consulting Limited

  • February 6, 2008 at 5:45 am

    #775538

    Sorry James,

    It's a "numbers" table that consists of nothing but a column of well indexed sequential numbers. Matt, Peter (Peso), and a lot of the others know about it. I mean't to convert the example to use spt_Values (which also has some numbers in it), but forgot.

    Here's how to make a Tally table... it has dozens of uses anywhere from simplifying "Split" routines to making sequential dates, etc...

    --===== Create and populate the Tally table on the fly

    SELECT TOP 11000 --equates to more than 30 years of dates

    IDENTITY(INT,1,1) AS N

    INTO dbo.Tally

    FROM Master.dbo.SysColumns sc1,

    Master.dbo.SysColumns sc2

    --===== Add a Primary Key to maximize performance

    ALTER TABLE dbo.Tally

    ADD CONSTRAINT PK_Tally_N

    PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

    --===== Allow the general public to use it

    GRANT SELECT ON dbo.Tally TO PUBLIC

    You can also get the numbers 0 to 255 in SQL Server 2000 by doing the following...

    SELECT NUMBER

    FROM Master.dbo.spt_Values

    WHERE Type = 'P'

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply