SQL Server Performance

Format numeric with 00.00

Discussion in 'SQL Server 2008 General Developer Questions' started by baburk, Mar 12, 2009.

  1. baburk New Member

    Hi,

    My column data type is [VHRBatchHrsKm] numeric(18, 2) NULL

    If I gave 0 it should store it as 00.00

    If I gave 1 it should store it as 01.00

    If I gave 1.1 it should store it as 01.10

    And also if I gave 125 also it should save it as 125.00
    But now if I give 1.0 it stores it a 1(I edited it in the table column itself)
    Since our client gives the column to store time in one condition(in the format hh.mm)
    and numeric value in another condition.

    I wants to the numeric value for time for calculation

    CONVERT(datetime,REPLACE(VHRBatchHrsKm,'.',':'),8)
    -CAST(DATEADD(millisecond,SUM(DATEDIFF(millisecond,0,CAST(TotalHrs AS DATETIME))),0) AS TIME)
    Thanks.
  2. FrankKalis Moderator

    Sorry, but if I understood correctly, this doesn't make sense to me. 0 is the same as 0.00 except for the representation. If you would ever go down that road to use one column to store distinct attributes you would need some additional information in that table such as how to interpret the stored value and then act accordingly.
  3. baburk New Member

    This Works for me
    DECLARE @PadChar CHAR(1)
    DECLARE @FinalLength INTEGER
    DECLARE @SourceString VARCHAR(19)
    SET @PadChar = '0'
    SET @FinalLength = 5
    SET @SourceString = '2'
    SELECT RIGHT( REPLICATE(@PadChar,@FinalLength - LEN(@FinalLength)) + CONVERT(VARCHAR(10),CONVERT(MONEY,2)), 5)
    SET @SourceString = '2.0'
    SELECT RIGHT( REPLICATE(@PadChar,@FinalLength - LEN(@FinalLength)) + CONVERT(VARCHAR(10),CONVERT(MONEY,@SourceString)), 5)
    SET @SourceString = '0.2'
    SELECT RIGHT( REPLICATE(@PadChar,@FinalLength - LEN(@FinalLength)) + CONVERT(VARCHAR(10),CONVERT(MONEY,@SourceString)), 5)
    SET @SourceString = '00.2'
    SELECT RIGHT( REPLICATE(@PadChar,@FinalLength - LEN(@FinalLength)) + CONVERT(VARCHAR(10),CONVERT(MONEY,@SourceString)), 5)
    SET @SourceString = '0.20'
    SELECT RIGHT( REPLICATE(@PadChar,@FinalLength - LEN(@FinalLength)) + CONVERT(VARCHAR(10),CONVERT(MONEY,@SourceString)), 5)
    Result
    02.00
    02.00
    00.20
    00.20
    00.20
  4. moh_hassan20 New Member

    you can display data in the format you need in your client application.



Share This Page