Tracker
HOME    TRACKER    MS SQL Server
 SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.

# How Do You Convert A Number From Negative To Positive?

## I was just wondering if there is a way of converting a number from negative to positive within Reporting Services, ie remove the "-" sign from the front of the number?  One of the columns within my report contains a calculation which results in an integer, but it is always a negative number, even though it should really be positive (it would take too long to describe the exact reason for this, but it's to do with the underlying database, which I don't have access to anyway). When I right-click on the relevant cell within the table in Layout view in the Report Designer and go to Expression, there is a list of Conversion functions under the "Common Functions" heading but I can't find anything there which would convert a number from negative to positive.  Does anyone know how this can be achieved?

Related Messages:
Group By Positive Or Negative Number In Table
Sorry if this question has been answered before--i tried a search but nothing came close to what i needed.

I have a report I am creating where I need to group on whether a number is positive or negative.  The number will never be zero.  This number is a decimal (currency), if that makes a difference.

I have tried creating a formula field with the following expression:

Code Snippet

IIf(Fields!Num.Value > 0, 1, 0)

I tried creating a group using this field (essentially, positive = 1 and negative = 0), but it gave me no results at all.

Convert Negative Number Stored As Nvarchar
I'm working with a horrible database!  The field I'm having issues with is a negative number stored as a nvarchar, but it's not stored in a consistent format.  I need to convert the field to a decimal (9,2) but I can't because of the negative sign.

Example:

Balance
00000000
0000-413
0000-913
00-10913
00009526

I don't even know where to start to convert this.  Any help is appreciated!

Thanks.

QA Returning Negative #s As Positive
Hey guys,

I have a very peculiar issue going on. I have a table that contains a decimal(18,2) column called "Amount". Looking at this table through Enterprise Mgr, I can see that there are values in there that are negative. However, when I run a query in Query Analyzer, it displays all the negative values as positives.

The only workaround I've found right now is to change the column type to "real" and then change it back to decimal(18,2), and it starts showing the negatives as negative. However, without performing this absurd workaround, it doesn't work.

Is there a known bug in QA that would manifest itself as this? What is the cause for this?

Positive Negative Values In SUM

Hi,

I have table with a column which has both negative and positive values.

I want to calculate the sum of positve values and the sum of negative values from that column separately. Is it possible.

Plase help me.

Ex: My  Input Values are

column1
-186.5499257
-141.8985222
-95.3846883
-71.1359768
-69.39071172
30.44750844
40.33666032
53.81527872
98.2967252
112.948667

OutPut goes like This

Column1                    Column2
-186.5499257               null
-141.8985222               null
-95.3846883                 null
-71.1359768                 null
-69.39071172            -564.36
30.44750844               null
40.33666032               null
53.81527872               null
98.2967252                 null
112.948667               335.84

Negative Numbers Become Positive When Transferring From 400
Help! I have a DTS package that does a data transfer between an AS400 and SQL 7. There is a field in the 400 table that contains negative numbers. After the transfer, the same field in the SQL table has lost the negative sign and is now positive. Also, the data in the original table has two decimal places. After the transfer, there are fifteen decimal places and it appears that it has been "unrounded", i.e. instead of 98.80, it's now 98.799999999999997.

Prior to upgrading to 7.0, we were doing the same thing with 6.5 using HDR to do the transfer. It worked perfectly then.

Anyone have any ideas??? Your help is MUCH appreciated.

Chris W.

How Do I Delibrately Make A Positive Value A Negative Value

I want to convert a positive value to a negative value.

How do I do it...

Treat Data Differently For Positive Vs Negative #
Ok, I have a table with IP addresses stored in decimal format using both positive and negative numbers.
The way that they are stored is:
Positve
1 thru 2147483647 = 0.0.0.1 - 127.255.255.255
Negative
-2147483648 thru -1 = 128.0.0.0 - 255.255.255.255
Conversion
positive
x/2^24 . (x/2^24)/2^16 . etc . etc
negative
(x+2^32)/2^24 . ((x+2^32)/2^24)/2^16 . etc . etc

I have a script which works by using UNION and the WHERE statements are x>0 x<0

My problem is I need to use a 3rd party app to run the script (McAfee ePO). McAfee does not recognize the UNION. My question is, can I acheive the same results as the script below, without using UNION.

SELECT ReportFullPathNode.FullPathName,
cast(cast(IPSubnetMask.IP_Start as bigint)/16777216 as varchar) + '.' +
cast(cast(IPSubnetMask.IP_Start as bigint)%16777216/65536 as varchar) + '.' +
cast(cast(IPSubnetMask.IP_Start as bigint)%16777216%65536/256 as varchar) + '.' +
cast(cast(IPSubnetMask.IP_End as bigint)/16777216 as varchar) + '.' +
cast(cast(IPSubnetMask.IP_End as bigint)%16777216/65536 as varchar) + '.' +
cast(cast(IPSubnetMask.IP_End as bigint)%16777216%65536/256 as varchar) + '.' +
UNION ALL
SELECT ReportFullPathNode.FullPathName,
cast(cast(4294967296+IPSubnetMask.IP_Start as bigint)/16777216 as varchar) + '.' +
cast(cast(4294967296+IPSubnetMask.IP_Start as bigint)%16777216/65536 as varchar) + '.' +
cast(cast(4294967296+IPSubnetMask.IP_Start as bigint)%16777216%65536/256 as varchar) + '.' +
cast(cast(4294967296+IPSubnetMask.IP_End as bigint)/16777216 as varchar) + '.' +
cast(cast(4294967296+IPSubnetMask.IP_End as bigint)%16777216/65536 as varchar) + '.' +
cast(cast(4294967296+IPSubnetMask.IP_End as bigint)%16777216%65536/256 as varchar) + '.' +

Stacked Column Bar With Negative And Positive Value. Cannot Cross At Zero

I've a stacked column chart (vertical) both with positive and negative value.

I want an horizontal line only for the value = 0.

I tried to add a serie and to plot it as line, but I see it in the legend and I don't want to see it.

Can I show the gridlines only for the value 0?
Simona

Creating A Blank Row In A Table Between Positive &&amp; Negative Values
I have a table that displays a bunch of data based on their percentage changes.

There is a parameter (let's say 5%) that the user can set to display only the data which either increased more than 5% or decreased more than 5%.

I want to have a space between the positive changes and the negative changes. ie, to create a blank row between the +'s and the -'s. Does anyone know of a way to do this?

Thanks,
Steven

Query Analyzer Shows Negative Numbers As Positive Numbers
Why does M\$ Query Analyzer display all numbers as positive, no matterwhether they are truly positive or negative ?I am having to cast each column to varchar to find out if there areany negative numbers being hidden from me :(I tried checking Tools/Options/Connections/Use Regional Settings bothon and off, stopping and restarting M\$ Query Analyer in betwixt, butno improvement.Am I missing some other option somewhere ?

Inserting Negative Number?
Hi

I'm a newbie when it comes to using SS7. When I try to insert a negative number (-1) into a integer column, the value is changed to 65535. How can overcome this problem?

TIA

JDJ
jdj@gvd.dk

Can't Convert Negative Numbers From String Format
Hello,
I'm having problems with a column of numeric string data coming from a tab delimited CSV file. When a number in the column is negative, it is expressed this way; 1,240.52-

The negative symbol occurs at the end of the numeric string. The destination column is in a SQL Server 2005 table. I've tried to change the data type of the table column, and three or four different data types in a data conversion task, but nothing has worked so far to bring the data over.

Any ideas?

cdun2

Meaning ??? Negative Number And Out Of Lock
Help

Why is the unsed space is negative number? Whould that affect the database? How do you fix the negative number?

If 'out of lock' happen, how do you check at what level the object was/is locked and how many locks is needed to configure?

DATEDIFF Returns Negative Number
I am trying to output the difference in minutes between 2 times. The sql statement works for the most part but if I compare a time before midnight and a time after midnight, the query returns a negative number.

Here is my SQL statement:

SELECT
dbo.fdc_trips.enrdate,dbo.FDC_Trips.enrtime, dbo.fdc_trips.atsdate,dbo.FDC_Trips.atstime, DATEDIFF(n, dbo.FDC_Trips.enrtime, dbo.FDC_Trips.atstime)
AS TimeDiff
FROM dbo.FDC_Trips

For example: if enrtime = 23:57:00 and atstime = 00:04:00, the DATEDIFF formula returns -1433, and really it should be 7.

How can I get this query to work with times before and after midnight?

Negative Number Parenthesis Alignment
I am  representing negative number within parenthesis , but as I am working on reporting service, it is possible to make the exact alignment of the number and parenthesis.

here is one example ....
when i format number with #,##,0;(#,##,0) i am getting output in a format  in subsequent rows like

123,345
(145,567)

How can I make this alignment like

123,345
(145,567)

where the parenthesis will be outside the number space keeping in mind the alignment is in right .

any help will be apriciated.

thanks
Aru

Sp_spaceused Report Negative Number For Unused
Could some one help me to explain what does it means when I ran sp_spaceused on the database it reported ex. -15430kb for unused. Does this mean that the index ran out off space. Do I need to increase space for the database. I will try to run the dbcc check hope it will fix the problem.

Thanks.

SIMPLE Command To Convert String To Number? Not CAST Or CONVERT.
Dear Experts,Ok, I hate to ask such a seemingly dumb question, but I'vealready spent far too much time on this. More that Iwould care to admit.In Sql server, how do I simply change a character into a number??????In Oracle, it is:select to_number(20.55)from dualTO_NUMBER(20.55)----------------20.55And we are on with our lives.In sql server, using the Northwinds database:SELECTr.regionid,STR(r.regionid,7,2) as a_string,CONVERT(numeric, STR(r.regionid,7,2)) as a_number,cast ( STR(r.regionid) as int ) as cast_to_numberFROM REGION R1 1.00112 2.00223 3.00334 4.0044SELECTr.regionid,STR(r.regionid,7,2) as a_string,CONVERT(numeric, STR(r.regionid,7,2) ) as a_number,cast (STR(r.regionid,7,2) as numeric ) as cast_to_numberFROM REGION R1 1.00112 2.00223 3.00334 4.0044Str converts from number to string in one motion.Isn't there a simple function in Sql Server to convertfrom string to number?What is the secret?Thanks

Convert A Number To Hex
Is there a existing stored proc to convert a integer is SQL Server to
a hex number?

How To Convert A Number To String In SQL?
I search the help for T-sql, still don't know how to convert a number, for example 12345 to a string with formating like 12,345

Convert Varchar To Number
Using SQL 2005. I have a field with dollar amounts but field type is VarChar. Need to convert to number to sum. What's the best way to do this or how can I sum a field type Varchar that has dollar amounts. Thank you, David

Convert Number To Time
In my database times are stored as integers. Therefore if someone arrives a 1am the value is 60, 2am 120 etc. Is there a way to convert these integer values to 1:00, 2:00. I've tried converting to a decimal so if somene arrives at 1:30 it returns 1.5, but I still only get the integer value...

CONVERT(decimal(10,2), (90/60)) AS iStart

Roy

Convert Varchar To Number
Hi

In my MSSSQL database I have a table that contains "AGE" and its datatype is varchar.

I want to calculate the min(age) and max(age).

But the AGE field's datatype is varchar and I canot do any mathematical functions.

In Oracle There is a code for doing that.I think this is the code

select max(tonumber(age));

what is it's MSSQL equivalent.

thanks

sabu

How To Convert 6.49146E+11 To NUMBER ?
Flat file has a data like 6.49146E+11.

How to convert this to 649146029310.

Whats the syntax for derived column component ?

Thanks.

How To Convert A Bit String To 0x Number?
hi,

I have a bit string like '000111010101101110000111' , how to convert it to varbinary format like 0x1D5B83?

Thanks.

How Can I Convert Datetime To Number Of Minutes
I have a column in a table that stores the number of hours a task took to do. The column TaskDuration is a datetime datatype. I need to convert the hours to something that can be summed. Does anyone how this can be done? I tried CONVERT(nvarchar(5), tblTasks.TaskDuration, 108) but of course the nvarchar(5) cannot be summed. Maybe there is a way to convert the time portion to minutes and divide it by 60, anyway if someone can offer some help I appreciate it.

Convert Month Into Its Number Equivalent...(Ex. JAN=1,FEB=2...)
Good Day to Everyone,

Need help!

I have this SQL table w/ fieldname "PayPeriod". Sample value of PayPeriod are SEP06-1, SEP06-2, OCT06-1, OCT06-2... Wherein the first 3 chars is the month, 06 is the year, 1 or 2 is the cutoff where 1 means the 2nd half of the previous month and 2 is the first half of the current month.

Now my questions are:
1.) How can I select these values sorted according to date. Since this is a character data type. Sorting is alphabetical. I need to sort it according to date where JAN05-1 is the first record and the last record is DEC06-2 (assuming that the oldest year is 05 and 06 is the latest).
2.) How can I convert the 3 char month into its number equivalent. Example, JAN is equal to 1, FEB is 2, MAR is 3....
3.) How can I count the no. of days in a month? Is there a function that would result to the no. of days in a given month?

Thank you so much in advance. :)

Convert Number (seconds To Datetime)
Hi
I have a field (int) thats a value in seconds.

I need to add it to a datetime value to get a finish time ..

how can i convert it to datetime (baring in mind its seconds)
so far any conversion i do converts it to days...

Convert Week Number Back To First Date
hi guys,
How could I convert 'week 9 2008' back into the first day of that week ?

is that even possible ?

How To Convert Number Into Fixed Format String.
I would like to convert a number into fixed format string. Say for example if I have number 5, I would like to show it as four charactered string:

0005.

In case if I have 33, then I would like to have a result like '0033'. Please let me know the string functions with which I can acheive this.

Convert From Number To Text- Exporting To Excel From SQL Reporting Services 2000
I was trying to export a report which contains a number format. When I do that, all the numbers in excel will have a green small tag beside it saying "Convert from Number to Text".

Is there anyway that I can change the format to a number when I export it to excel?

Positive Value

I have the following query

OutPut:

ChildName     BillPayerName     Cost
Mary                Fred                          £50

Total Balance                                 Blank

The problem is when I check for a value > 00.1 in the having clause the total balance column disappears.
But its ok if I use any other comparison operator.
I.e < 00.1.

Any ideas.

create procedure Rpt_pos1 --(@cmb1 as varchar(50)) with ENCRYPTION as

--declare @startdate smalldatetime

AS

set dateformat ymd

set datefirst 7

--loop through weeks

create table #getTable (thesite integer null,thechild integer null,cost money null)

insert #getTable select s.siteid,c.childid,0

from site s,child c

where --s.siteName=@cmb1

c.siteID=s.siteID

and c.active=1

and c.potential=0

insert #getTable

select s.siteid,c.childid,isnull(sum(round(bt.total,2,2)),0)*-1

from site s,child c,billingTotal bt

where --s.siteName=@cmb1

s.siteID=bt.siteid

and c.siteID=s.siteID

and bt.siteID=s.siteID

and bt.childid=c.childid

and c.active=1

and c.potential=0

group by s.siteID,c.childid

insert #getTable select s.siteid,c.childid,isnull(sum(round(p.amount,2,2)),0)

from site s,child c,payment p

where --s.siteName=@cmb1

p.siteID=s.siteid

and c.siteID=s.siteID

and p.childID=c.childID

and c.active=1

and c.potential=0

group by s.siteID,c.childid

create table #results (ChildName varchar(200) null,BillPayerName varchar(200) null, Cost varchar(20) null)

insert

#results

select c.forename + ' ' + c.surname

,case b.telhome when '' then b.Forename + ' ' + b.Surname

else b.Forename + ' ' + b.Surname + ' (' + b.telhome + ')' end

,case when sum(round(g.cost,2,2)) < 0 then cu.Symbol + cast(sum(round(g.cost,2,2)) as varchar(15))

when sum(round(g.cost,2,2)) > 0 then cu.Symbol + cast(sum(round(g.cost,2,2)) as varchar(15))

when sum(round(g.cost,2,2)) = 0 then cu.Symbol + cast(sum(round(g.cost,2,2)) as varchar(15)) end

from

#gettable g

,child c

,billpayer b

,currency cu

,site s

where

cu.currencyid=s.currency

and s.siteID=c.siteID

and c.siteID=g.thesite

and c.childID=g.thechild

and b.billpayerID=c.billPayerID

group by

c.forename,

c.surname,

b.telhome,

b.surname,

b.forename,

c.siteID,

cu.symbol

having sum(round(g.cost,2,2)) > 00.1

order by

c.surname asc

insert #results

select '','',''

insert #results

select

'Total Balance'

,''

,c.Symbol + cast(sum(round(g.cost,2,2)) as varchar(15))

from

#gettable g

,site s

,currency c

where

g.thesite=s.siteid

and c.currencyID=s.currency

group by

c.symbol

having sum(round(g.cost,2,2)) > 00.1

drop table #gettable

select * from #results

drop table #results

go

Check For Positive Value
I need to be able to check & display people who have a positive value in the bills.
i.e > 1p.
On the billingtotal table i need to sum > 1p.

CREATE PROCEDURE rpt_PositiveAccountBalances

(@cmb1 as varchar(100)) WITH ENCRYPTION

AS

BEGIN

SELECT DISTINCT

Site.siteName,

Child.surname + ', ' + Child.forename AS ChildsName,

billPayer.surname + ', ' + billPayer.forename AS BillPayerName,

BillingTotal.total

FROM

Site

INNER JOIN

Child

ON

Site.siteID = Child.siteID

INNER JOIN

BillingTotal

ON

Site.siteID = BillingTotal.siteID

CROSS JOIN

billPayer

WHERE

site.sitename=@cmb1

END

Nagative To Positive, Format
one value should be always positive in report

I just replace "-" to " " using  Replace(Fields!sumamount.Value,"-","") and format is currency like \$ 1234.12

but after replace I get 1234.1200 in report

format of cell is currency like \$ 1234.12 and if I don't use replace - it's ok

how to remove two last zero and add "\$" ? or may be other ideas how to show alway positive value ?

Showing The + Sign As Prefix For Positive Numbers
Hi,

I would like to know how to Format a textbox to show the positive sign (+) in the even that the number more than zero.

I would prefer to do this without writing an specific IIf function into the expression if possible.

The common formats provided did not specify anything on handling prefix positive negative signs.

Thanks.

Kenny

Convert Week Number To Date Of 1st Day Of That Week...

I'm looking for a way to convert the [week number, year] back to the date for the 1st day of that week.

Example (assuming DATEFIRST has been set to 1) : Week 3, 2008 = January 14, 2008

Is there a SQL function that will do this?

Thanks,
Mike

Negative Zero
Query: Select -1*0.00
Result: -0.00

Maja

Converting Negative Value
Hi!I have a large table width values like this:000024634300000-37500000002783868891000009603857000-60000000000001672396000000195200000010315112000017000000I need to convert the numbers into an integer-type field. Is thispossible with just native sql-commands? I don't want to make anactive-x script because it will be very slow on the large table.Espen

Negative Values...
calculating profit... how do I write an update query that will correct the gross profit calculated column for all negative qty transactions

Basically in the stored procedure that creates the table the query includes:

Profit = CASE Sale WHEN 0 THEN 0 ELSE (Sale - Cost) END,
which is wrong when sale and cost is negative the formula becomes
(-Sale + Cost).... I want it to be -(Sale-Cost) (where sale any cost ignores negative sign.....

but i dont know to write this....any ideas???

Negative Numbers
I`m using an ascii file to bcp into sql tables. A balance field comes in as leading sign (-435). It`s loaded into tables as -435. I bcp it out to a file later and it comes out as -435. Is there a command or a way to get trailing signs without having to parse through the field and moving the negative sign to the end (435-)?

TIA,
Morgan

Negative Values

I have the following query, I would like to know if I can replace negative values with a zero and if a posiive value appears then leave the value alone?

SELECT     TimeStamp, Tag1,Tag2,Tag3
FROM         dbo.keller8

How To Round With Negative Numbers?
I am using a select statement to obtain a result set back with aggregateddata. The problem is that I am seeing column data with 11 to 13 digitsafter the decimal point. I tried using the STR function, but then the OrderBy clause does not sort properly because there are negative numbers in theaggregated data... I tried using Round, but that does no good either - itstill ends up displaying too many digits after the decimal point. Right nowI'm just using Query Analyzer to display the data, so I can live with it fornow. But, in the future, my app will be getting a result set back and Iwould prefer not to have to go through each row and do a round on it fromthe program. Does anyone know how to solve this problem?Thanks for any help,Bob

Replace Negative Values
hi,

In the result of a function in my query, there are negative numbers.

How do I replace them with a 0 or is there a function like ISNULL that replaces the values that are negative?

thanks,

maarten

Negative Unused Space
I wrote simple script to check space used by tables:

CREATE TABLE #SpaceUsed(
TableName NVARCHAR(128),
NoOfRows INT,
Reserved NVARCHAR(18),
Data NVARCHAR(18),
Index_Size NVARCHAR(18),
Unused NVARCHAR(18)
)
GO
sp_msforeachtable "INSERT INTO #SpaceUsed EXEC sp_spaceused '?'"

SELECT * FROM #SpaceUsed

SELECT
CAST(Sum(CAST(Replace(Reserved,' KB','') AS INT)) AS NVARCHAR) + ' KB' AS TotalReserved,
CAST(Sum(CAST(Replace(Data,' KB','') AS INT)) AS NVARCHAR) + ' KB' AS TotalData,
CAST(Sum(CAST(Replace(Index_Size,' KB','') AS INT)) AS NVARCHAR) + ' KB' AS TotalIndex_Size,
CAST(Sum(CAST(Replace(Unused,' KB','') AS INT)) AS NVARCHAR) + ' KB' AS TotalUnused
FROM #SpaceUsed

DROP TABLE #SpaceUsed

and one of results looks strange to me:

TableName NoOfRows Reserved Data Index_Size Unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
T_TableXX 50081 38024 KB 37432 KB 640 KB -48 KB

Anyone know reason of such result (negative value of unused space)?

Negative Device Size
SQL Enterprise manager is reporting my database device size to be a negative number.
Primarily, this is the case for the log files, but also occurs on a few of the .dat files.

Is there a correction? Is this a display error, or an actual size conflict/

Device Sizes Are Negative
Ok, I have a new one. Several of my devices are showing with negative sizes when viewed in edit in enterprise manager. I cannot edit them as the change now button is grayed-out. Oddly enough they are all located on the same drive. The master (on C drive), and the tempdb (on D drive) both show as the default device. I am very confused. User access to the information is fine. What gives?

Sp_spaceused With Negative Numbers?
Hello:

I have gotten the following information from sp_spaceused:

----------------------------------------------------------------------
database_name database_size unallocated space
------------------------------ ------------------ ------------------
EP_tran2 700.00 MB 273.21 MB

reserved data index_size unused
------------------ ------------------ ------------------ ------------------
437034 KB 295106 KB 1447796 KB -1305868 KB
--------------------------------------------------------------------

I unfortunately since this is for a test database that I have put the data and log on the same device.

My questions are:

1) Why do I have negative numbers?

3) for a live database can I drop the log and put the log on another device? What do I need to do?

4) has anybody seen this before?

5)what can I use to get accurate numbers?

THanks. Any information will be greatly appreciated.

DAvid Spaisman