Arithmetic Overflow MSG..HELP!

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

SQL Server Administration (2000)

Arithmetic Overflow MSG..HELP!

Author

Topic

SqlZ
Yak Posting Veteran

69 Posts

Posted - 2002-02-28 : 11:27:11

Here is the situation.

I am trying to split a DOB (Date Of Birth) up to strip the YOB (Year of Birth) and the AGE of someone into individual columns labeled as such. Sounds pretty straight forward and a piece of cake using the DATEPART and DATEDIFF functions right, wrong. Here's the kinker, the DOB column is a nvarchar(12) datatype and when I use the following code to CAST it as a smalldatetime I get the

Server: Msg 8115, Level 16, State 2, Line 21
Arithmetic overflow error converting expression to data type datetime.

error message. Any insight to what I should do would be appreciated to accomplish this.

Here is my code. I tried to split it up into small increments and still no luck.
--------------------------------------------------------------
DECLARE @omnusaID int
DECLARE @YOB int
DECLARE @AGE int
DECLARE @DOB nvarchar(12)

SELECT @omnusaID=1
WHILE @omnusaID<100

BEGIN

SELECT @YOB=DATEPART(yy,(CAST(DOB AS datetime))),
@AGE=DATEDIFF(yy,CAST(DOB AS datetime),Getdate())
FROM omnusa
WHERE omnusaID=@omnusaID

UPDATE omnusa
SET YOB=@YOB,
AGE=@AGE
WHERE omnusaID=@omnusaID

SELECT @omnusaID=@omnusaID+1
END
--------------------------------------------------------------

========================
My username represents the two things that consume most of my time.
SQL, and my Z

efelito
Constraint Violating Yak Guru

478 Posts

Posted - 2002-02-28 : 11:37:13

What format is the DOB field currently in? YYYYMMDD or DDMMYYYY or MMDDYYYY? In order to just cast it as a date it will need to be in the order that matches your region settings.

Jeff Banschbach
Consultant, MCDBA

SqlZ
Yak Posting Veteran

69 Posts

Posted - 2002-02-28 : 11:46:44

I found the problem. All data was supposed to be MMDDYYY but some of it is DDMMYYYY. I should have checked before I ran my script. I think I am going to have to set a DATEFORMAT somewhere in my code. Thanks for your response efelito.

========================
My username represents the two things that consume most of my time.
SQL, and my Z

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources