June 9, 2011 at 8:29 am
Hello, I get error message from this code:
set @Number = CONVERT(INT,@Number))
The conversion of the varchar value '2011060910105711742 ' overflowed an int column. Maximum integer value exceeded.
How can I fix it?
Thank you
June 9, 2011 at 8:33 am
you'll need a larger data type...bigint might hold it.
the largest int is ~2 billion...your value's a few orders larger than that.
select convert(bigint,2011060910105711742)
Lowell
June 9, 2011 at 8:55 am
Thank you,but I still get the same error when I do this
case when
[section] = 'C'
then
@Number
when [section] = 'A' then @Number + 1
else null
end as [Number]
June 9, 2011 at 9:07 am
where is the DECLARE @Number bigint statement?
where is the SET Number = CONVERT(bigint,????) statment?
what you posted is disconnected from my perspective...iu think you need to show more of the offending code.
Lowell
June 9, 2011 at 10:47 am
We definitely need to see not only more code (because what you posted doesn't give us enough information), but we need to see table structure and sample data (if the @Number is coming from a table).
Where are you getting such a large number from anyway? EDIT: It almost looks like a faux datetime stamp.
June 9, 2011 at 1:16 pm
Sorry,here is my code:
declare @Number char(20);
set @Number = convert(nvarchar(50),rand())
set @Number = '1' + substring(@Number,3,4)
set @Number = convert(int,@Number)
DECLARE @dd VARCHAR(20)
SET @dd = CONVERT(VARCHAR(20), GETDATE(), 20)
PRINT '
current date:' + @dd
SET @dd = REPLACE ( REPLACE ( REPLACE ( @dd, '-', ''), ':', ''), ' ' , '')
PRINT '
formatted
current date:' + @dd
SET @Number = @dd + @Number
PRINT '
@Number
:' + @Number
select
case when
[Type] = 'C'
then
@Number
when [Type] = 'A' then @Number + 1
else null
end as [Number] into table1
June 9, 2011 at 1:19 pm
Ok, what's the point here?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 9, 2011 at 1:22 pm
i AM TRYING NOT TO get a duplicate key IN the record. I need to make follow a format of yymmddrrrrr where yymmdd is year-month-day and rrrrr is the existing random number.
June 9, 2011 at 1:46 pm
Wouldn't an identity column be easier? Or even a GUID?
Why this convoluted approach?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 9, 2011 at 1:53 pm
They don't like big numbers and but I am wondering if this bases on each time interface runs generates unique number
June 9, 2011 at 2:12 pm
Hang on... They don't like big numbers so you're using a number that's in the general vicinity of a billion billion (1018) What am I missing here?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 9, 2011 at 3:23 pm
Using an identity doesn't mean that you have a large number. You could calculate a column of the date + identity if needed.
Alternatively, why not just calculate the "next" number for that date?
June 10, 2011 at 3:00 am
I'm confused as well. I'm assuming "they" is your client. But the fact that they don't like big numbers and you are generating a random 50 character number that you are then truncating down to 20 characters confuses me.
If your client is looking for a faux datetime stamp, there are better ways to do this. This is the one I use.
Declare @Number char(17);
SELECT @Number = REPLACE(REPLACE(REPLACE(REPLACE(Convert(char(23),GetDate(),121)
,'-',''),':',''),' ',''),'.','');
SELECT Convert(bigint,@Number);
--And to see why I replaced your length with 17 and how this works, run the below code.
SELECT REPLACE(REPLACE(REPLACE(REPLACE(Convert(char(23),GetDate(),121)
,'-',''),':',''),' ',''),'.',''), GetDate(),
LEN(REPLACE(REPLACE(REPLACE(REPLACE(Convert(char(23),GetDate(),121)
,'-',''),':',''),' ',''),'.',''))
Remember that if your client doesn't like big numbers, then this probably isn't a solution either. Because to them, it will look like a big number, despite the fact that this is a datetime value down to the millisecond. If they have a problem, then you need to discuss solutions with them. Not just substitute another big number for the big numbers they're trying to avoid.
EDIT: You say you're looking for random numbers that are unique. There really is no good way to do non-unique random numbers, though I did write an article about it here[/url]. You will notice, if you read the comments on the article, that even this isn't a truly random number generation system. Even Identity doesn't work as a true measure of uniqueness. The only true measure of unique would be a database-wide GUID and that's a large number.
Please clarify what you're trying to accomplish with your code (in English, not code) and what your client constraints are that are causing you to jump through hoops. That will help us help you.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply