Arithmetic overflow - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Arithmetic overflow

Author

Topic

Kurmanc
Yak Posting Veteran

92 Posts

Posted - 2008-04-28 : 04:03:08

Hello,

I am trying to get a value from a select-query that is of the datatype int. The code below will not, I think it is because I mix up datetime with int conversion. Anyway, I am not really sure how to subtract

Select datediff(day,convert(int, SUBSTRING('198001010012', 1, 8)),convert(int, getdate()))

Results in: Arithmetic overflow error converting expression to data type datetime..

I really need to get the difference because in my next select query I will use the ">" , "<" and "=" to filter the result.

If I do not convert and use this code

SELECT name, datediff(day, SUBSTRING(clientno, 1, 8), getdate()), email
FROM ...

I get this message:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

clientno is of data type char(12)

Any suggestions?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-28 : 04:05:58

quote:
Originally posted by Kurmanc

Hello,

I am trying to get a value from a select-query that is of the datatype int. The code below will not, I think it is because I mix up datetime with int conversion. Anyway, I am not really sure how to subtract

Select datediff(day,convert(int, SUBSTRING('198001010012', 1, 8)),convert(int, getdate()))

Results in: Arithmetic overflow error converting expression to data type datetime..

I really need to get the difference because in my next select query I will use the ">" , "<" and "=" to filter the result.

If I do not convert and use this code
SELECT name, datediff(day, SUBSTRING(clientno, 1, 8), getdate()), email
FROM ...

I get this message:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

clientno is of data type char(12)

Any suggestions?

Why are you converting it to int. convert to datetime instead

Select datediff(day,convert(datetime, SUBSTRING('198001010012', 1, 8)),convert(datetime, getdate()))

Kurmanc
Yak Posting Veteran

92 Posts

Posted - 2008-04-28 : 04:13:11

Hello visakh16,

Thanks for your reply. It is true, the int conversion is stupid :)

Your advice works only if I have this

...WHERE clientno LIKE '19%

But if I also have '20%', like below it wont work

...WHERE clientno LIKE '19%' OR clientno LIKE '20%'

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Why will it complain on the "OR"?

Kurmanc
Yak Posting Veteran

92 Posts

Posted - 2008-04-28 : 04:16:23

Hm interesting..

These works:

WHERE clientno LIKE '19%'

WHERE clientno LIKE '20%'

But not:

WHERE clientno LIKE '19%' OR clientno LIKE '20%'

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-28 : 04:20:27

Whats the datatype of clentno? can you provide some sample data from your tables?

Kurmanc
Yak Posting Veteran

92 Posts

Posted - 2008-04-28 : 04:30:22

clientno is char(12), example:

198001010011
195002020112
200102050234

etc..

I use substring to get rid of the four last char's, so only the year, month, and day of birth is left.

Kurmanc
Yak Posting Veteran

92 Posts

Posted - 2008-04-28 : 04:43:03

[code]CREATE View vwClientsByAge
AS
SELECT datediff(day,convert(datetime, SUBSTRING(clientno, 1, 8)),convert(datetime, getdate())) /(365+ISDATE(02/29/ + YEAR(getdate()))) AS Age
FROM tblclient
WHERE clientno like '19%' or clientno like '20%'[/code]

This will work
[code]SELECT * from vwClientsByAge
WHERE age like '8'[/code]

This will not work
[code]SELECT * from vwClientsByAge
WHERE age = 8[/code]

----------

[code]CREATE View vwClientsByAge
AS
SELECT datediff(day,convert(datetime, SUBSTRING(clientno, 1, 8)),convert(datetime, getdate())) /(365+ISDATE(02/29/ + YEAR(getdate()))) AS Age
FROM tblclient
WHERE clientno like '19%' [/code]

If I get rid of one of these:
"clientno like '19%'"
"clientno like '20%'"

This will work
[code]SELECT * from vwClientsByAge
WHERE age < 15[/code]

But then I dont get the people born after year 2000

----------

So strange..

Kurmanc
Yak Posting Veteran

92 Posts

Posted - 2008-04-28 : 04:46:36

I am sorry, the code I sent above did surprizingly work!

But not this below..the difference is not so big..


CREATE View vwClientsByAge
AS
SELECT *
FROM
(	
SELECT ClientId, ClientNo, FirstName, LastName, Address1, ZipCode, City, PhoneHome, PhoneWork, PhoneCellular, Email, datediff(day,convert(datetime, SUBSTRING(clientno, 1, 8)),convert(datetime, getdate())) /(365+ISDATE(02/29/ + YEAR(getdate()))) as age, CONVERT(VARCHAR(10), DATEADD(YEAR, 18, CAST(SUBSTRING(clientno, 1, 8) AS DATETIME)), 120) AS Authoritative 
FROM tblclient 
WHERE clientno like '19%' or clientno like '20%'
) AS T

Kurmanc
Yak Posting Veteran

92 Posts

Posted - 2008-04-28 : 04:58:58

Ok, after a while of thinking I got it work! :)

Kurmanc
Yak Posting Veteran

92 Posts

Posted - 2008-04-28 : 05:01:03

So, this works (very ugly, but it works)

CREATE View vwClientsByAge
AS
SELECT ClientId, C.ClientNo, FirstName, LastName, Address1, ZipCode, City, PhoneHome, PhoneWork, PhoneCellular, Email, x.age, x.Authoritative
FROM tblClient C
INNER JOIN
(SELECT  CX.clientno, datediff(day,convert(datetime, SUBSTRING(clientno, 1, 8)),convert(datetime, getdate())) /(365+ISDATE(02/29/ + YEAR(getdate()))) as age, CONVERT(VARCHAR(10), DATEADD(YEAR, 18, CAST(SUBSTRING(clientno, 1, 8) AS DATETIME)), 120) AS Authoritative 
FROM tblClient CX
WHERE clientno like '19%' or clientno like '20%') AS X on X.clientno = C.clientno

Now I can search for people born 19% och 20% :)

Kurmanc
Yak Posting Veteran

92 Posts

Posted - 2008-04-28 : 05:02:05

Thanks for your time visakh16.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources