Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 122
  • Last Modified:

SQL Help Convert Cast Query

I have a table with the following data

ID      Max Value
1      1.56
2      10.753
3      100.008
4      18.6
5      .4
6      2.47
7      1.49
8      13.65
9      29.68
10      37.22

I want to select all records, but have Max Value displayed like 000.000.

For Example

ID      Max Value
1      001.560
2      010.753
3      100.008
4      018.600
5      000.400
6      002.470
7      001.490
8      013.650
9      029.680
10      037.220

Any help is appreciated
0
PeelSeel2
Asked:
PeelSeel2
  • 3
  • 2
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I want to select all records, but have Max Value displayed like 000.000.
The below code block works, but couple of things you'll have to work out...
What's the data type of MaxValue?  If something other then numeric(?, 3) then you'll have to handle the three decimal points in the query by using CAST(? as numeric(?,3) in the inside of MaxValue
How to handle values 1,000 or greater, or negative numbers?
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
	DROP TABLE #tmp
GO

CREATE TABLE #tmp (id int, MaxValue numeric(19,3))

INSERT INTO #tmp (id, MaxValue) 
VALUES
(1,1.56), (2,10.753), (3,100.008), (4,18.6), (5,.4), 
(6,2.47), (7,1.49), (8,13.65), (9,29.68), (10,37.22)

SELECT id, RIGHT('00' + CAST(MaxValue as varchar(100)),7)
FROM #tmp

Open in new window

0
 
PeelSeel2Author Commented:
I've requested that this question be deleted for the following reason:

Found answer not relating to posted solution.
0
 
PeelSeel2Author Commented:
This worked

select id,
, format(Max Value, '000.000') as value

from table
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
... Ok, and did what I gave you not work?
0
 
PeelSeel2Author Commented:
Your solution works great.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now