converting (casting) from decimal(24,4) to decimal(21,4) data type problem

Hello!

I would like to cast (convert) data type decimal(24,4) to

decimal(21,4). I could not do this using standard casting function

CAST(@.variable as decimal(21,4)) or CONVERT(decimal(21,4),@.variable)

because of the following error: "Arithmetic overflow error converting

numeric to data type numeric." Is that because of possible loss of the

value?

Thanks for giving me any advice,

Ziga

What was the value? For a value that fits in both, you should have no issue:

declare @.value decimal(24,4)
set @.value = 10.12

select cast(@.value as decimal(21,4))
go

But if the non-fractional value is too large for the 21,4 datatype, it will go boom:

declare @.value decimal(24,4)
set @.value = 12345678901234567890.1234

select @.value

select cast(@.value as decimal(21,4))

12345678901234567890.1234

Msg 8115, Level 16, State 8, Line 6
Arithmetic overflow error converting numeric to data type numeric.

If this isn't the case, then post the code that fails, the value, and the results of:

select @.@.version

I tried this on the following versions:

Microsoft SQL Server 2000 - 8.00.679 (Intel X86)
Aug 26 2002 15:09:48
Copyright (c) 1988-2000 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

Microsoft SQL Server 2005 - 9.00.2153.00 (Intel X86)
May 8 2006 22:41:28
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

|||Yes the non fraction value is to large... So there is no way to solve this problem?|||What is the result that you expect when you try to cast a larger value? CAST will throw error for overflow and that is the only expected behavior. If you want to fit larger values into smaller data type then you need to truncate the value yourself or perform other logic. You could use CASE expression to check for the larger values and conditionally perform cast. Also, take a look at ROUND function. You could use it instead of CAST for the larger values or for the whole conversion.|||

Thanks!

Actually I am performing some mappings between two systems. Interface for the destination system has specification of the field as decimal(21,4). In source system the (calculated) value is larger - decimal(24,4). For those large values where the cast (to decimal(21,4)) is not possible (arithemtic overflow) performing correct mapping is just not possible...

Thakns a lot,

Ziga

|||You still haven't answered how you would like to handle the larger values. Do you simply throw those away? What would it mean to store a truncated result in the database? And if you use it later then you are going to make wrong assumptions. It seems like your table schema is wrong and if you want to retain the higher precision values you need to modify the schema to match the source or vice versa. Otherwise, you will have to use round or truncate the value yourself before inserting and you cannot use CAST.

Convert SSN (varchar) to Number

Is casting really necessary? Could you try:
select
max (SSN)
from
MyTable
where
SSN between '001330000' and '001379999'
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<ILCSP@.NETZERO.NET> wrote in message
news:1151436829.800467.247610@.p79g2000cwp.googlegroups.com...
Hello, I'm trying to get the Max number from a range of Social Security
numbers in my SQL 2000 Customer table. The SSNs as stored as
varchar(9).
I'm supposed to get the SSN with the 'highest' numbers from 001330000
to 001379999 range.
I know that number should be 001340062, but I never got it. I've tried
to convert it and cast it, but so far I haven't been able to do so.
When I converted it, I got only 1340062 (it was missing the first 2
zeroes).
We're creating fakes SSNs for people who don't have one and I'm
supposed to know which one is the max we've created so far and then add
1 to it and so on and so on. This worked in Access, but when we
upgraded to SQL it didn't work anymore.
Any help would be appreciated it.
Thanks.Take a look at this
select RIGHT('000000000' + CONVERT(VARCHAR(9),12345),9)
change 12345 to your output
Denis the SQL Menace
http://sqlservercode.blogspot.com/
ILCSP@.NETZERO.NET wrote:
> Hello, I'm trying to get the Max number from a range of Social Security
> numbers in my SQL 2000 Customer table. The SSNs as stored as
> varchar(9).
> I'm supposed to get the SSN with the 'highest' numbers from 001330000
> to 001379999 range.
> I know that number should be 001340062, but I never got it. I've tried
> to convert it and cast it, but so far I haven't been able to do so.
> When I converted it, I got only 1340062 (it was missing the first 2
> zeroes).
> We're creating fakes SSNs for people who don't have one and I'm
> supposed to know which one is the max we've created so far and then add
> 1 to it and so on and so on. This worked in Access, but when we
> upgraded to SQL it didn't work anymore.
> Any help would be appreciated it.
> Thanks.|||What does Homeland Security think about you creating 'fake SSNs'?
g,d,r
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
<ILCSP@.NETZERO.NET> wrote in message
news:1151436829.800467.247610@.p79g2000cwp.googlegroups.com...
> Hello, I'm trying to get the Max number from a range of Social Security
> numbers in my SQL 2000 Customer table. The SSNs as stored as
> varchar(9).
> I'm supposed to get the SSN with the 'highest' numbers from 001330000
> to 001379999 range.
> I know that number should be 001340062, but I never got it. I've tried
> to convert it and cast it, but so far I haven't been able to do so.
> When I converted it, I got only 1340062 (it was missing the first 2
> zeroes).
> We're creating fakes SSNs for people who don't have one and I'm
> supposed to know which one is the max we've created so far and then add
> 1 to it and so on and so on. This worked in Access, but when we
> upgraded to SQL it didn't work anymore.
> Any help would be appreciated it.
> Thanks.
>|||Arnie Rowland wrote:
> What does Homeland Security think about you creating 'fake SSNs'?
> g,d,r
>
Uh-oh, now you're forever associated with the phrase "fake SSN" - NSA
will be looking for you... :-)
Oh crap, now me too...|||What if you create a 'fake' SSN for someone and then later on someone
signs up with that SSN, then what happens? Hint: Big Headache since
you have to assign a new 'fake' SSN to the old person and changes this
probably all over the place
Better to use XXXX00001 or some other Format like FAKE00001 etc etc
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Arnie Rowland wrote:[vbcol=seagreen]
> What does Homeland Security think about you creating 'fake SSNs'?
> g,d,r
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> <ILCSP@.NETZERO.NET> wrote in message
> news:1151436829.800467.247610@.p79g2000cwp.googlegroups.com...|||"Arnie Rowland" <arnie@.1568.com> wrote in message
news:eEVMRNimGHA.4700@.TK2MSFTNGP05.phx.gbl...

> What does Homeland Security think about you creating 'fake SSNs'?
> g,d,r
> --
> Arnie Rowland, YACE*
I would think they are already contacting
CoreComm - Voyager, Inc
East Lansing, Michigan|||Hello all, I used Tom's way and it works great. Thanks for replying.
I think I should have not used the phrase fake SSNs, but I guess I just
wanted to get some attention.
:-)
Tom Moreau wrote:
> Is casting really necessary? Could you try:
> select
> max (SSN)
> from
> MyTable
> where
> SSN between '001330000' and '001379999'
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> <ILCSP@.NETZERO.NET> wrote in message
> news:1151436829.800467.247610@.p79g2000cwp.googlegroups.com...
> Hello, I'm trying to get the Max number from a range of Social Security
> numbers in my SQL 2000 Customer table. The SSNs as stored as
> varchar(9).
> I'm supposed to get the SSN with the 'highest' numbers from 001330000
> to 001379999 range.
> I know that number should be 001340062, but I never got it. I've tried
> to convert it and cast it, but so far I haven't been able to do so.
> When I converted it, I got only 1340062 (it was missing the first 2
> zeroes).
> We're creating fakes SSNs for people who don't have one and I'm
> supposed to know which one is the max we've created so far and then add
> 1 to it and so on and so on. This worked in Access, but when we
> upgraded to SQL it didn't work anymore.
> Any help would be appreciated it.
> Thanks.|||You're guarenteed to be high on the DHS/NSA watch list!
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
<ILCSP@.NETZERO.NET> wrote in message
news:1151438702.180395.194640@.u72g2000cwu.googlegroups.com...
> Hello all, I used Tom's way and it works great. Thanks for replying.
> I think I should have not used the phrase fake SSNs, but I guess I just
> wanted to get some attention.
> :-)
>
> Tom Moreau wrote:
>|||Just FYI, 001-xx-xxxx codes are reserved by New Hampshire. I wouldn't
advise assigning "fake SSN's" to begin with, but I *definitely* wouldn't
assign them in a range that is currently in use by a state. That's just
asking for trouble.
According to the SSA, any valid number beginning with "000" will never be a
valid SSN. Here are some links to help with the business rules:
http://www.socialsecurity.gov/employer/highgroup.txt
http://www.socialsecurity.gov/employer/stateweb.htm
Given the three numbers you've passed in, the SELECT below will grab the
MAX(). It sounds like you want to add one and left pad it with zeroes.
Here's a sample:
CREATE FUNCTION dbo.AddOneAndPadSSN(@.ssn VARCHAR(9))
RETURNS VARCHAR(9)
AS
BEGIN
DECLARE @.iSSN INT
SELECT @.iSSN = CAST(@.ssn AS INT) + 1
DECLARE @.newSSN VARCHAR(9)
SELECT @.newSSN = RIGHT ('000000000' + CAST(@.iSSN AS VARCHAR(9)), 9)
RETURN @.newSSN
END
GO
CREATE TABLE #temp(ssn VARCHAR(9))
INSERT INTO #temp(ssn)
SELECT '001330000'
UNION SELECT '001379999'
UNION SELECT '001340062'
SELECT dbo.AddOneAndPadSSN(MAX(ssn))
FROM #temp
DROP TABLE #temp
<ILCSP@.NETZERO.NET> wrote in message
news:1151436829.800467.247610@.p79g2000cwp.googlegroups.com...
> Hello, I'm trying to get the Max number from a range of Social Security
> numbers in my SQL 2000 Customer table. The SSNs as stored as
> varchar(9).
> I'm supposed to get the SSN with the 'highest' numbers from 001330000
> to 001379999 range.
> I know that number should be 001340062, but I never got it. I've tried
> to convert it and cast it, but so far I haven't been able to do so.
> When I converted it, I got only 1340062 (it was missing the first 2
> zeroes).
> We're creating fakes SSNs for people who don't have one and I'm
> supposed to know which one is the max we've created so far and then add
> 1 to it and so on and so on. This worked in Access, but when we
> upgraded to SQL it didn't work anymore.
> Any help would be appreciated it.
> Thanks.
>|||Nobody here but us wannabe fashion clothes reps...
"Michael Kujawa" <nof at kujawas dot net> wrote in message
news:OH8N2TimGHA.3632@.TK2MSFTNGP03.phx.gbl...
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:eEVMRNimGHA.4700@.TK2MSFTNGP05.phx.gbl...
>
>
> I would think they are already contacting
> CoreComm - Voyager, Inc
> East Lansing, Michigan
>