I have a requirement like in the target table the Balance is in Varchar as it has to show negative sign coming from the source.but they want the decimal places to show in the target table.For example if the source is(10000-),my target should show 100.00-I am not sure how to convert this.any thoughts?
First, I feel compelled to point out that SQL is not "supposed" to be used as a presentation formatter. That said, there are ways to do what you are asking. I noticed you put the negative sign on the right, but I don't know if that's just a locale/culture setting. Regardless, the easiest way to do this is to use the FORMAT function available in SQL Server 2012 and newer. The first FORMAT of each SELECT below assumes the location of the minus sign is culture-based; the second forces it to be at the end. DECLARE @balance decimal(19,6) = -123456789.789123; SELECT FORMAT(@balance, '0.00'), FORMAT(@balance, '0.00;0.00-'); SET @balance = 0; SELECT FORMAT(@balance, '0.00'), FORMAT(@balance, '0.00;0.00-');
I got the solution to my problem,Here it is: SELECT LTRIM(RTRIM(CASE WHEn CHARINDEX('-',[ballance]) >0 THEN Convert(VARCHAR(200),(CAST((Replace([ballance],'-',' ' )) AS Float)/100))+ '-' ELSE Convert(VARCHAR(250),(CAST([ballance] AS Float)/100))END)) AS Ballance FROM [dbo].[CheckData]