Results 1 to 8 of 8

Thread: Select query

  1. #1
    Join Date
    Sep 2003
    Location
    Pune, India
    Posts
    4

    Unanswered: Select query

    Hi I have two tables and I am using these tabler to retrieve some data
    my query is

    Select AccrualDate, AccrualId, AmountAccrued as OldAccrual, ((SELECT MaxCap FROM Tiers WHERE StartYear <= (CAST(YEAR(AccrualDate) as int) - CAST(YEAR(@HireDate) as int)) AND EndYear >= (CAST(YEAR(AccrualDate) as int) - CAST(YEAR(@HireDate) as int)))
    )/24 as NewAccrual from Accruals
    where AmountAccrued NOT IN (select maxcap/24 from Tiers) order by AccrualDate desc

    @Hireate is a variable I am passing in this query, there are 4 records in Tiers table and contained 160, 176, 192, 208 in MaxCap field
    this query is executing inside a Stored Proc, I am retrieving this in ASP
    In my query I am geting maxcap 160 after applying some condition and it should return me 6.666666666666667 but instead of that it returns me 6.66666666666667
    why this is happening the data type of this NewAccrual is float
    any one know about the proble

  2. #2
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    First, I have to mention that the YEAR function returns an INT, so you don't need to CAST it (should make things a little more readable).

    Instead of Float, try using Numeric, as you can specify the precision you would like to use. In this case, I think you are looking for NUMERIC(18,15).

    [The accuracy of the result is what you are questioning, correct?]
    -bpd

  3. #3
    Join Date
    Sep 2003
    Posts
    39
    You should most probably convert(varchar, intResult) and send that to your VB app.

    Often, Sql Server Data Types are longer (ie, more precise) than the VB ones. For example, a Sql Server INT would need a LONG in VB.

    If you don't need to do any fancy calculations, then you can just convert(varchar, intValue) to get your values.

  4. #4
    Join Date
    Sep 2003
    Location
    Pune, India
    Posts
    4
    Hi
    The Database design has been already and it is working already
    i cann't change the datatype of any field

  5. #5
    Join Date
    Sep 2003
    Posts
    39
    You don't have to change the datatype of the field B(

    You only have to pass it as a different data type. Lookup Convert() in BOL.

  6. #6
    Join Date
    Sep 2003
    Location
    Pune, India
    Posts
    4
    I have tried it after using convert function to cast into varchar but still problem persisting

  7. #7
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    If you cast it to VARCHAR, you get a string result that looks exactly the same as the FLOAT result. CAST it to NUMERIC(18,15). If that doesnot work, then try the super ugly CAST(CAST(result AS NUMERIC(18,15)) AS VARCHAR(30)).
    -bpd

  8. #8
    Join Date
    Sep 2003
    Location
    Pune, India
    Posts
    4

    Talking

    Hi
    Thanx a lot, Now it's working

Posting Permissions

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