Post reply

Syntax to convert from ounces to pounds in format of 6 lb 3 oz

  • June 3, 2015 at 5:22 am

    #318685

    I'm very new to sql and it's syntax (access users), I've played around with various combinations, how would I get the weight to display as pounds and ounces? the field type is varchar

    This works to convert, but number doesn't display the way I want

    (dbo.BIRTH_WT_NUM / 0.035274) AS [Birth weight in grams]

  • June 3, 2015 at 5:40 am

    #1802558

    help us help you!

    CREATE TABLE and sample data get you help fast!

    i put this example together that might help you visualize it.

    i got the formula for ounces to grams from google.

    CREATE TABLE #MySampleData(gramweight decimal (19,6))

    INSERT INTO #MySampleData

    SELECT 3175.15 UNION ALL

    SELECT 3000.15 UNION ALL

    SELECT 5172.15 UNION ALL

    SELECT 4200.15

    /*

    TotalOunces gramweight Pounds Ounces

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

    112.03775582215 3175.150000 7 0

    105.86273817925 3000.150000 6 9

    182.50352858151 5172.150000 11 6

    148.20571630204 4200.150000 9 4

    */

    ;WITH MyCTE

    AS

    (

    SELECT (gramweight / 28.34 ) AS TotalOunces,gramweight

    FROM #MySampleData

    )

    SELECT TotalOunces,gramweight,

    CONVERT(INT,TotalOunces) / 16 As Pounds ,

    CONVERT(INT,TotalOunces) % 16 AS Ounces

    FROM MyCTE

    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!

  • June 3, 2015 at 6:04 am

    #1802575

    The next question would be, do you understand how and why Lowell's solution works?

    --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 3 posts - 1 through 2 (of 2 total)

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