Format numeric with 2 decimal places

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Format numeric with 2 decimal places

Author

Topic

baburk
Posting Yak Master

108 Posts

Posted - 2009-03-12 : 02:04:44

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

How Can I.

But now if I give 1.0 it stores it a 1(I edited it in the table column itself)

How can I able to format the number.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-12 : 02:43:30

u want to store it as
00.00
01.00

then use convert or cast fuctions
'0'+cast(value as varchar(32))

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-12 : 02:48:30

quote:
Originally posted by baburk

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

How Can I.

But now if I give 1.0 it stores it a 1(I edited it in the table column itself)

How can I able to format the number.

declare @number numeric(18, 2)
set @number=1.0
select @number

Why is leading 0 important for you?

Madhivanan

Failing to plan is Planning to fail

baburk
Posting Yak Master

108 Posts

Posted - 2009-03-12 : 02:52:50

Hi madhivanan,

Thanks for your reply

Since our client gives the column to store time in one condition
and numeric value in another condition.

I wants to the numeric to time for calculation

CONVERT(datetime,REPLACE(numericfield,'.',':'),8)
-CAST(DATEADD(millisecond,SUM(DATEDIFF(millisecond,0,CAST(VRL.TotalHrs AS DATETIME))),0) AS TIME)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-12 : 13:30:30

quote:
Originally posted by baburk

Hi madhivanan,

Thanks for your reply

Since our client gives the column to store time in one condition
and numeric value in another condition.

I wants to the numeric to time for calculation

CONVERT(datetime,REPLACE(numericfield,'.',':'),8)
-CAST(DATEADD(millisecond,SUM(DATEDIFF(millisecond,0,CAST(VRL.TotalHrs AS DATETIME))),0) AS TIME)

please note that for storing leading 0's you've make column as varchar which will make your manipulations complex.

baburk
Posting Yak Master

108 Posts

Posted - 2009-03-13 : 01:15:14

quote:
Originally posted by visakh16

quote:
Originally posted by baburk

Hi madhivanan,

Thanks for your reply

Since our client gives the column to store time in one condition
and numeric value in another condition.

I wants to the numeric to time for calculation

CONVERT(datetime,REPLACE(numericfield,'.',':'),8)
-CAST(DATEADD(millisecond,SUM(DATEDIFF(millisecond,0,CAST(VRL.TotalHrs AS DATETIME))),0) AS TIME)

please note that for storing leading 0's you've make column as varchar which will make your manipulations complex.

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)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources