Site Sponsored By: SQLDSC - SQL Server Desired State Configuration
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.
do you know how to display data like this 028.0000can you help me for the script?
Luuk123
Yak Posting Veteran
52 Posts
Posted - 2013-03-11 : 03:53:08
You have to convert/cast it to a varchar. An integer/decimal etc. would remove the first 0.
bandi
Master Smack Fu Yak Hacker
2242 Posts
Posted - 2013-03-11 : 05:47:26
see this example...
;WITH t(c) AS(SELECT 1.99 UNION ALL SELECT 21.34 UNION ALL SELECT 1797.94 UNION ALL SELECT -300.36 UNION ALL SELECT -21.99 UNION ALL SELECT -2.31 ) SELECT CASE WHEN SIGN(c) = 1 THEN '' ELSE '-' END + REPLACE(STR(ABS(c), 7, 2), ' ','0') AS 'LeadingZerosWithSigns', REPLACE(STR(ABS(c), 7, 2), ' ','0') AS 'LeadingZerosWithOutSigns'FROM t
--Chandu
visakh16
Very Important crosS Applying yaK Herder
52326 Posts
Posted - 2013-03-11 : 06:36:33
how is your actual data existing in the table?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
kabon
Starting Member
48 Posts
Posted - 2013-03-13 : 00:17:50
for example like this :select 2.00Result : 2.00select convert (numeric(20,4),2.00)Result : 2.0000how to make 002.0000
LoztInSpace
Aged Yak Warrior
940 Posts
Posted - 2013-03-13 : 00:41:12
Without a specific reason, you should generally do that in the presentation layer rather than the database.So why is it 002.0000 or 028.0000 and not 000000000000000002.0000 or 000028.0000 ?What's your criteria?Anyway, if you do think it has to be done in the database (and you should think twice) then bandi has already answered it for you.