Using a Plunger to Stop an Arithmetic Overflow

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Using a Plunger to Stop an Arithmetic Overflow

Author

Topic

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-09-19 : 13:25:41

I keep getting a stupid arithmetic overflow on a date conversion. Here's my sample code.....

DROP TABLE tab_NEWLIST
DROP TABLE POTUS_SAMPLE

CREATE TABLE POTUS_SAMPLE
(
SEQ int NULL ,
P_NAME nvarchar (10) NULL,
P_BORN nvarchar (08) NULL,
P_TBEG nvarchar (08) NULL,
P_TEND nvarchar (08) NULL,
P_DIED nvarchar (08) NULL
)
GO

INSERT INTO POTUS_SAMPLE VALUES('01','WASHINGTON', '17320222','17890430','17970304','17991214')
INSERT INTO POTUS_SAMPLE VALUES('08','VAN BUREN' , '17821205','18370304','18410304','18620724')
INSERT INTO POTUS_SAMPLE VALUES('11','POLK' , '17951102','18450304','18490304','18490615')
INSERT INTO POTUS_SAMPLE VALUES('13','FILLMORE' , '18000107','18500709','18530304','18740308')
INSERT INTO POTUS_SAMPLE VALUES('21','ARTHUR' , '18291005','18810919','18850304','18861118')
INSERT INTO POTUS_SAMPLE VALUES('26','ROOSEVELT' , '18581027','19010914','19090304','19190106')
INSERT INTO POTUS_SAMPLE VALUES('30','COOLIDGE' , '18720704','19230802','19290304','19330105')
INSERT INTO POTUS_SAMPLE VALUES('37','NIXON' , '19130109','19690120','19740809','19940422')
INSERT INTO POTUS_SAMPLE VALUES('40','REAGAN' , '19110206','19810120','19890120','20040605')
INSERT INTO POTUS_SAMPLE VALUES('43','BUSH' , '19460706','20010120','20090120',' ')
GO

SELECT SEQ, P_NAME, CONVERT(varchar(10),ISNULL([P_BORN],'00/00/0000'),120) as BIRTH,
MONTH([P_BORN]) as _MONTH
INTO tab_NEWLIST
FROM POTUS_SAMPLE
WHERE (DATEPART(YEAR,[P_BORN]) < 1900)
GO

This is the error I get when I plug this into the SQA:

Server: Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.

Any help is appreciated. (I sure wish SQL was more forgiving on dates!)

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-19 : 13:32:43

'17320222' this one is lower that the lowest date allowed
so you can't use date functions on it.

Go with the flow & have fun! Else fight the flow

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-09-19 : 13:35:40

quote:
Originally posted by spirit1

'17320222' this one is lower that the lowest date allowed
so you can't use date functions on it.

Go with the flow & have fun! Else fight the flow

So any date outside the range noted in the WHERE statement will cause this?

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-19 : 13:37:16

yup.

Go with the flow & have fun! Else fight the flow

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-19 : 13:47:54

Xerxes,

You'll have a problem in the SELECT portion as well. Any place where you are trying to use a date function, such as MONTH, it must be a valid datetime date.

Tara

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-09-19 : 14:00:16

Tara,

That is why I used CONVERT(varchar(10),ISNULL([P_BORN],'00/00/0000'),120) as BIRTH so that I'd be able to handle possible garbage data. So you're telling me that this is no good, too?

If that's the case, how would you re-write this differently?

Thanks!

Xerx

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-19 : 14:06:30

The CONVERT portion isn't the part that is breaking. The part that is breaking is the use of the date functions, MONTH and DATEPART. You can't use any date functions if your dates are outside of the datetime range.

This'll probably work for your WHERE clause:
WHERE P_BORN < '19000000'

For the Month column, you'll either have to handle that with your presentation layer or use SUBSTRING to get the fifth and sixth characters.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-19 : 14:09:48

Oh the CONVERT is probably breaking too, just noticed you are using a style, 120, which applies to datetime data. You probably can't use that either.

Tara

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-09-19 : 14:19:42

Tara,

The code suggestion: "WHERE P-BORN < 19000000"---that doesn't work either. Makes me wonder what I CAN use.

Xerx.

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-09-19 : 14:29:25

Working sample:


DROP TABLE #tab_NEWLIST
DROP TABLE #POTUS_SAMPLE

CREATE TABLE #POTUS_SAMPLE 
(
SEQ int NULL ,
P_NAME nvarchar (10) NULL,
P_BORN nvarchar (08) NULL,
P_TBEG nvarchar (08) NULL,
P_TEND nvarchar (08) NULL,
P_DIED nvarchar (08) NULL
)
GO

INSERT INTO #POTUS_SAMPLE VALUES('01','WASHINGTON', '17320222','17890430','17970304','17991214')
INSERT INTO #POTUS_SAMPLE VALUES('08','VAN BUREN' , '17821205','18370304','18410304','18620724')
INSERT INTO #POTUS_SAMPLE VALUES('11','POLK' , '17951102','18450304','18490304','18490615')
INSERT INTO #POTUS_SAMPLE VALUES('13','FILLMORE' , '18000107','18500709','18530304','18740308')
INSERT INTO #POTUS_SAMPLE VALUES('21','ARTHUR' , '18291005','18810919','18850304','18861118')
INSERT INTO #POTUS_SAMPLE VALUES('26','ROOSEVELT' , '18581027','19010914','19090304','19190106')
INSERT INTO #POTUS_SAMPLE VALUES('30','COOLIDGE' , '18720704','19230802','19290304','19330105')
INSERT INTO #POTUS_SAMPLE VALUES('37','NIXON' , '19130109','19690120','19740809','19940422')
INSERT INTO #POTUS_SAMPLE VALUES('40','REAGAN' , '19110206','19810120','19890120','20040605')
INSERT INTO #POTUS_SAMPLE VALUES('43','BUSH' , '19460706','20010120','20090120',' ')
GO

SELECT 
	SEQ, 
	P_NAME, 
	BIRTH = stuff(right([P_BORN],4),3,0,'\') + '\'  +left([P_BORN],4),
	_MONTH = convert(int,substring([P_BORN],5,2)) 
INTO #tab_NEWLIST
FROM #POTUS_SAMPLE
--WHERE (DATEPART(YEAR,[P_BORN]) < 1900)
WHERE [P_BORN] < '19000000'

Select * From #tab_NEWLIST

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-19 : 14:35:17

quote:
Originally posted by Xerxes

Tara,

The code suggestion: "WHERE P-BORN < 19000000"---that doesn't work either. Makes me wonder what I CAN use.

Xerx.

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Do you have single quotes around the 19000000? Do you get the same error?

Tara

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-09-19 : 14:36:35

Thanks, Corey!

If I was running an IT shop, I'd be hiring you!

Xerxes
"Allowed to avoid oblivion in favor of mere obscurity"

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-09-19 : 15:33:02

Since no one else mentioned it, you can use the ISDATE function to verify that a string is a valid SQL Server date. It can be any string that SQL Server can convert to a date.

Example below with 2 good and 2 bad dates:


select
	In_date,
	Good_Date =
		case
		when isdate(In_date) = 1
		then convert(datetime,in_date)
		else null
		end
from
	(
	select In_date = '20010120'	union all
	select In_date = '17521231'	union all
	select In_date = '17530229'	union all
	select In_date = '99991231'
	) a

CODO ERGO SUM

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-09-19 : 15:37:28

Thanks, Mike!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-09-19 : 15:47:52

Wish you wouldn't remind me of beer while I'm at work.

quote:
Originally posted by Xerxes

Thanks, Mike!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

CODO ERGO SUM

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-09-19 : 15:51:48

Sorry, it's all I think of!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-09-19 : 18:00:55

quote:
Originally posted by Xerxes

Thanks, Corey!

If I was running an IT shop, I'd be hiring you!

Xerxes
"Allowed to avoid oblivion in favor of mere obscurity"

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Why Thanks!

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources