convert -ve int to +ve bigint

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » convert -ve int to +ve bigint

convert -ve int to +ve bigint

Rate Topic

Display Mode

Topic Options

Author

Message

chinni2383

Posted Wednesday, January 02, 2013 9:23 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, April 04, 2013 10:54 AM
Points: 4, Visits: 13

Hi,

How do you convert the -ve int value to +ve bigint value.

I have used cast and convert which didn't help.

Example.

declare @bigIntCard bigint

declare @Card int

Set @Card = -199870816
Set @bigIntCardField1 = cast(@Card as bigint)

print @bigIntCardField1

I expected it to be casted as a +ve bigint value.

Is there a way to do it? Please help me.

Thanks

Post #1401911

Abu Dina

Posted Wednesday, January 02, 2013 9:29 AM

SSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 10:34 AM
Points: 494, Visits: 2,158

How about:

Set @bigIntCardField1 = @Card * -1

SQL server does an implicit conversion from int to bigint so no need to cast @Card

-----------------------------------
http://www.SQL4n00bs.com

Post #1401914

Jason-299789

Posted Wednesday, January 02, 2013 9:34 AM

SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 1:30 AM
Points: 803, Visits: 2,124

I believe the int gets to a max of 2147483648 before it flips over to negatives, and it then starts off at -2,147,483,648 and counts down towards 0, as 2^32 is represented as -1.

So I think you would need to do

Bigint value =2147483648+ABS(-2,147,483,648)+Value.

so I beleive this is the code (needs testing though).

declare @bigIntCard bigint 

declare @Card int

Set @Card = -199870816
Set @bigIntCard = Cast(2147483648 as bigint)+((Cast(2147483648 as bigint))-@Card)

print @bigIntCard

EDIT : Maths was wrong and corrected. Not sure if you need to add one.

If you plug -1 into this you get the number 4294967298 which is 2^32.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Post #1401919

chinni2383

Posted Wednesday, January 02, 2013 9:42 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, April 04, 2013 10:54 AM
Points: 4, Visits: 13

The +ve value should be 2305969845.

It is stored as a -ve number in the column when the datatype is int. But in one stored proc I need to convert it into a bigint which should be converted into an +ve number

Post #1401925

Jason-299789

Posted Wednesday, January 02, 2013 9:46 AM

SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 1:30 AM
Points: 803, Visits: 2,124

How did you get to this number 2305969845 as I dont get close even by just adding the abs(-199870816) to the top value for an Integer.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Post #1401930

Jason-299789

Posted Wednesday, January 02, 2013 10:21 AM

SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 1:30 AM
Points: 803, Visits: 2,124

Sorry by INT Max value wrong it should read

declare @bigIntCard bigint 

declare @Card int

Set @Card = -199870816
Set @bigIntCard = Cast(2147483647 as bigint)+((Cast(2147483648 as bigint))+@Card)+1
print @bigIntCard

You also need the +1, otherwise it doesnt work.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Post #1401954

vliet

Posted Thursday, January 03, 2013 2:34 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, May 20, 2013 11:34 PM
Points: 47, Visits: 266

(CAST(@Card AS BIGINT) & 4294967295) should do the job for any card value

Post #1402249

chinni2383

Posted Thursday, January 03, 2013 9:38 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, April 04, 2013 10:54 AM
Points: 4, Visits: 13

Thank you guys.

Post #1402452

« Prev Topic | Next Topic »

Permissions