Solved

ERROR: Arithmetic overflow error converting expression to data type datetime

Posted on 2009-05-08
30
901 Views
Last Modified: 2012-06-27
I run the code below to get number of records per company (in row) and per month (in columns) but got the error:
"Arithmetic overflow error converting expression to data type datetime"

Date filed data type is nvarchar, does it need to be converted first? if yes how would the code look like?
Any idea?
use DB_Mydatabase

select

	[Company],

	(case when month([Date Field]) = 1 then count(*) else 0 end) as Jan,

	(case when month([Date Field]) = 2 then count(*) else 0 end) as Feb,

	(case when month([Date Field]) = 3 then count(*) else 0 end) as Mar,

	(case when month([Date Field]) = 4 then count(*) else 0 end) as Apr,

	(case when month([Date Field]) = 5 then count(*) else 0 end) as May,

	(case when month([Date Field]) = 6 then count(*) else 0 end) as Jun,

	(case when month([Date Field]) = 7 then count(*) else 0 end) as Jul,

	(case when month([Date Field]) = 8 then count(*) else 0 end) as Aug,

	(case when month([Date Field]) = 9 then count(*) else 0 end) as Sep,

	(case when month([Date Field]) = 10 then count(*) else 0 end) as Oct,

	(case when month([Date Field]) = 11 then count(*) else 0 end) as Nov,

	(case when month([Date Field]) = 12 then count(*) else 0 end) as Dec,

	Count(*) as TotalYear

from 

	dbo.MySourceTable

group by 

	[Company],

	[Date Field]

Order by

	TotalYear

Open in new window

0
Comment
Question by:TechNovation
  • 11
  • 8
  • 7
  • +1
30 Comments
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24334879
use

datepart(m,[Date Field])

instead of
month([Date Field])
0
 
LVL 6

Expert Comment

by:bokist
ID: 24335005
if date field is nvarchar - you can handle it without conversion :

case when substring([date field],4,2) = '01' then count(*) else 0 end) as Jan ... &  so on
 
substring part depend on date format in Date field  
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24335285
>>if date field is nvarchar - you can handle it without conversion :<<

date field is not nvarchar, it clearly shows in error message.
0
 
LVL 6

Expert Comment

by:bokist
ID: 24335305
I just read the question....

"Date filed data type is nvarchar, does it need to be converted first? if yes how would the code look like?
 Any idea?"
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24335324
error message is saying something else and author is saying something else. I don't know what is right neither you. ;) lets wait for author response.
0
 
LVL 6

Expert Comment

by:bokist
ID: 24335343
That's agreed !
0
 

Author Comment

by:TechNovation
ID: 24335566
I just looked at the data type of the field in SQL table and it says "nvarchar". To be homest I don't know what the problem is exactly?
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24335572
have you tested my code?
0
 
LVL 31

Assisted Solution

by:RiteshShah
RiteshShah earned 100 total points
ID: 24335589
Moreover, if you are doing all this for question posted at

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_24389831.html#a24335567

than don't need to do this. have a look I gave you query there.
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 24341509
Your [Date Field] is stored as nvarchar as you state but when you try to use the MONTH() function on it, it is implicitly trying to convert the contents of that field to a date using the default date format of the database and one of the elements of the date is not valid for where it is in the string or there is just a completely wrong value in that field, like all zeros or something.  Check your data and post some examples if you can not figure it out.
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 24341620
When you do get your dates straight then the query has another flaw.  The [Date Field] in the group by will cause data like this:
Company      Jan      Feb      Mar      Apr      May      Jun      Jul      Aug      Sep      Oct      Nov      Dec      TotalYear
Company 1      1      0      0      0      0      0      0      0      0      0      0      0      1
Company 3      1      0      0      0      0      0      0      0      0      0      0      0      1
Company 2      0      0      0      0      0      0      0      0      0      1      0      0      1
Company 1      0      1      0      0      0      0      0      0      0      0      0      0      1
Company 3      0      0      0      0      0      1      0      0      0      0      0      0      1
Company 2      0      0      0      0      0      0      0      0      1      0      0      0      1

Use the attached snippet to get output like this:
Company      Jan      Feb      Mar      Apr      May      Jun      Jul      Aug      Sep      Oct      Nov      Dec      TotalYear
Company 1      1      1      0      0      0      0      0      0      0      0      0      0      2
Company 2      0      0      0      0      0      0      0      0      1      1      0      0      2
Company 3      1      0      0      0      0      1      0      0      0      0      0      0      2
select

	[Company],

	Sum(case when month([Date Field]) = 1 then 1 else 0 end) as Jan,

	Sum(case when month([Date Field]) = 2 then 1 else 0 end) as Feb,

	Sum(case when month([Date Field]) = 3 then 1 else 0 end) as Mar,

	Sum(case when month([Date Field]) = 4 then 1 else 0 end) as Apr,

	Sum(case when month([Date Field]) = 5 then 1 else 0 end) as May,

	Sum(case when month([Date Field]) = 6 then 1 else 0 end) as Jun,

	Sum(case when month([Date Field]) = 7 then 1 else 0 end) as Jul,

	Sum(case when month([Date Field]) = 8 then 1 else 0 end) as Aug,

	Sum(case when month([Date Field]) = 9 then 1 else 0 end) as Sep,

	Sum(case when month([Date Field]) = 10 then 1 else 0 end) as Oct,

	Sum(case when month([Date Field]) = 11 then 1 else 0 end) as Nov,

	Sum(case when month([Date Field]) = 12 then 1 else 0 end) as Dec,

	Count(*) as TotalYear

from 

	MySourceTable

group by 

	[Company]

Order by

	TotalYear

Open in new window

0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24341632
what about this?



select

        [Company],

        Sum(case when month(convert(varchar,[Date Field],120)) = 1 then 1 else 0 end) as Jan,

        Sum(case when month(convert(varchar,[Date Field],120)) = 2 then 1 else 0 end) as Feb,

        Sum(case when month(convert(varchar,[Date Field],120)) = 3 then 1 else 0 end) as Mar,

        Sum(case when month(convert(varchar,[Date Field],120)) = 4 then 1 else 0 end) as Apr,

        Sum(case when month(convert(varchar,[Date Field],120)) = 5 then 1 else 0 end) as May,

        Sum(case when month(convert(varchar,[Date Field],120)) = 6 then 1 else 0 end) as Jun,

        Sum(case when month(convert(varchar,[Date Field],120)) = 7 then 1 else 0 end) as Jul,

        Sum(case when month(convert(varchar,[Date Field],120)) = 8 then 1 else 0 end) as Aug,

        Sum(case when month(convert(varchar,[Date Field],120)) = 9 then 1 else 0 end) as Sep,

        Sum(case when month(convert(varchar,[Date Field],120)) = 10 then 1 else 0 end) as Oct,

        Sum(case when month(convert(varchar,[Date Field],120)) = 11 then 1 else 0 end) as Nov,

        Sum(case when month(convert(varchar,[Date Field],120)) = 12 then 1 else 0 end) as Dec,

        Count(*) as TotalYear

from 

        MySourceTable

group by 

        [Company]

Order by

        TotalYear

Open in new window

0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24341636
and if you don't want SUM but just COUNT than here is your tool,



select

        [Company],

        count(case when month(convert(varchar,[Date Field],120)) = 1 then 1 else 0 end) as Jan,

        count(case when month(convert(varchar,[Date Field],120)) = 2 then 1 else 0 end) as Feb,

        count(case when month(convert(varchar,[Date Field],120)) = 3 then 1 else 0 end) as Mar,

        count(case when month(convert(varchar,[Date Field],120)) = 4 then 1 else 0 end) as Apr,

        count(case when month(convert(varchar,[Date Field],120)) = 5 then 1 else 0 end) as May,

        count(case when month(convert(varchar,[Date Field],120)) = 6 then 1 else 0 end) as Jun,

        count(case when month(convert(varchar,[Date Field],120)) = 7 then 1 else 0 end) as Jul,

        count(case when month(convert(varchar,[Date Field],120)) = 8 then 1 else 0 end) as Aug,

        count(case when month(convert(varchar,[Date Field],120)) = 9 then 1 else 0 end) as Sep,

        count(case when month(convert(varchar,[Date Field],120)) = 10 then 1 else 0 end) as Oct,

        count(case when month(convert(varchar,[Date Field],120)) = 11 then 1 else 0 end) as Nov,

        count(case when month(convert(varchar,[Date Field],120)) = 12 then 1 else 0 end) as Dec,

        Count(*) as TotalYear

from 

        MySourceTable

group by 

        [Company]

Order by

        TotalYear

Open in new window

0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 24341833
RiteshShah,
You cannot COUNT and use "then 1 else 0", it will Count both of those and not just the 1 and all your months values will be the same as TotalYear.
TechNovation,
If you want to use Count instead of Sum then change all the "Else 0" to "Else NULL".
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24341840
CGLuttrell, thanks, what about this? did it as per your suggestion.
select

        [Company],

        count(case when month(convert(varchar,[Date Field],120)) = 1 then 1 else NULL END) as Jan,

        count(case when month(convert(varchar,[Date Field],120)) = 2 then 1 else NULL END) as Feb,

        count(case when month(convert(varchar,[Date Field],120)) = 3 then 1 else NULL END) as Mar,

        count(case when month(convert(varchar,[Date Field],120)) = 4 then 1 else NULL END) as Apr,

        count(case when month(convert(varchar,[Date Field],120)) = 5 then 1 else NULL END) as May,

        count(case when month(convert(varchar,[Date Field],120)) = 6 then 1 else NULL END) as Jun,

        count(case when month(convert(varchar,[Date Field],120)) = 7 then 1 else NULL END) as Jul,

        count(case when month(convert(varchar,[Date Field],120)) = 8 then 1 else NULL END) as Aug,

        count(case when month(convert(varchar,[Date Field],120)) = 9 then 1 else NULL END) as Sep,

        count(case when month(convert(varchar,[Date Field],120)) = 10 then 1 else NULL END) as Oct,

        count(case when month(convert(varchar,[Date Field],120)) = 11 then 1 else NULL END) as Nov,

        count(case when month(convert(varchar,[Date Field],120)) = 12 then 1 else NULL END) as Dec,

        Count(*) as TotalYear

from 

        MySourceTable

group by 

        [Company]

Order by

        TotalYear

Open in new window

0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:TechNovation
ID: 24344791
used the code above (latest) but got ERROR:

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Warning: Null value is eliminated by an aggregate or other SET operation.

Any idea?
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24344861
what about this script?




select

        [Company],

        count(case when  convert(int,isnull([Date Field],0)) = 1 then 1 else NULL END) as Jan,

        count(case when  convert(int,isnull([Date Field],0)  ) = 2 then 1 else NULL END) as Feb,

        count(case when  convert(int,isnull([Date Field],0)  ) = 3 then 1 else NULL END) as Mar,

        count(case when  convert(int,isnull([Date Field],0)  ) = 4 then 1 else NULL END) as Apr,

        count(case when  convert(int,isnull([Date Field],0)  ) = 5 then 1 else NULL END) as May,

        count(case when  convert(int,isnull([Date Field],0)  ) = 6 then 1 else NULL END) as Jun,

        count(case when  convert(int,isnull([Date Field],0)  ) = 7 then 1 else NULL END) as Jul,

        count(case when  convert(int,isnull([Date Field],0)  ) = 8 then 1 else NULL END) as Aug,

        count(case when  convert(int,isnull([Date Field],0)  ) = 9 then 1 else NULL END) as Sep,

        count(case when  convert(int,isnull([Date Field],0)  ) = 10 then 1 else NULL END) as Oct,

        count(case when  convert(int,isnull([Date Field],0)  ) = 11 then 1 else NULL END) as Nov,

        count(case when  convert(int,isnull([Date Field],0)  ) = 12 then 1 else NULL END) as Dec,

        Count(*) as TotalYear

from 

        MySourceTable

group by 

        [Company]

Order by

        TotalYear

Open in new window

0
 

Author Comment

by:TechNovation
ID: 24344924
It returns 0 for all cells expect the TotalYear
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24344933
BTW, do you want count or sum?
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24344948
why don't you try this one, just to check?



select

        [Company],

        Sum(case when  convert(int,is0([Date Field],0)) = 1 then 1 else 0 END) as Jan,

        Sum(case when  convert(int,is0([Date Field],0)  ) = 2 then 1 else 0 END) as Feb,

        Sum(case when  convert(int,is0([Date Field],0)  ) = 3 then 1 else 0 END) as Mar,

        Sum(case when  convert(int,is0([Date Field],0)  ) = 4 then 1 else 0 END) as Apr,

        Sum(case when  convert(int,is0([Date Field],0)  ) = 5 then 1 else 0 END) as May,

        Sum(case when  convert(int,is0([Date Field],0)  ) = 6 then 1 else 0 END) as Jun,

        Sum(case when  convert(int,is0([Date Field],0)  ) = 7 then 1 else 0 END) as Jul,

        Sum(case when  convert(int,is0([Date Field],0)  ) = 8 then 1 else 0 END) as Aug,

        Sum(case when  convert(int,is0([Date Field],0)  ) = 9 then 1 else 0 END) as Sep,

        Sum(case when  convert(int,is0([Date Field],0)  ) = 10 then 1 else 0 END) as Oct,

        Sum(case when  convert(int,is0([Date Field],0)  ) = 11 then 1 else 0 END) as Nov,

        Sum(case when  convert(int,is0([Date Field],0)  ) = 12 then 1 else 0 END) as Dec,

        Sum(*) as TotalYear

from 

        MySourceTable

group by 

        [Company]

Order by

        TotalYear

Open in new window

0
 

Author Comment

by:TechNovation
ID: 24345659
I get error:
"'is0' is not a recognized built-in function name."
For this I need sum.
0
 

Author Comment

by:TechNovation
ID: 24345683
just to show you what the Date Field data looks like:
20080630
20081231
20070512

I need to grap the '06', '12', '05',... from it
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 24345704
ok good, thats what we needed to get the convert right.
select

        [Company],

        count(case when month(convert(varchar,[Date Field],112)) = 1 then 1 else NULL END) as Jan,

        count(case when month(convert(varchar,[Date Field],112)) = 2 then 1 else NULL END) as Feb,

        count(case when month(convert(varchar,[Date Field],112)) = 3 then 1 else NULL END) as Mar,

        count(case when month(convert(varchar,[Date Field],112)) = 4 then 1 else NULL END) as Apr,

        count(case when month(convert(varchar,[Date Field],112)) = 5 then 1 else NULL END) as May,

        count(case when month(convert(varchar,[Date Field],112)) = 6 then 1 else NULL END) as Jun,

        count(case when month(convert(varchar,[Date Field],112)) = 7 then 1 else NULL END) as Jul,

        count(case when month(convert(varchar,[Date Field],112)) = 8 then 1 else NULL END) as Aug,

        count(case when month(convert(varchar,[Date Field],112)) = 9 then 1 else NULL END) as Sep,

        count(case when month(convert(varchar,[Date Field],112)) = 10 then 1 else NULL END) as Oct,

        count(case when month(convert(varchar,[Date Field],112)) = 11 then 1 else NULL END) as Nov,

        count(case when month(convert(varchar,[Date Field],112)) = 12 then 1 else NULL END) as Dec,

        Count(*) as TotalYear

from 

        MySourceTable

group by 

        [Company]

Order by

        TotalYear

Open in new window

0
 

Author Comment

by:TechNovation
ID: 24345756
Got error:
"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Warning: Null value is eliminated by an aggregate or other SET operation."
0
 
LVL 26

Accepted Solution

by:
Chris Luttrell earned 400 total points
ID: 24345772
ok, then we are back to you must have a value in the nvarchar field that is not in the date format.
can you do
select distinct substring([date field],5,2)
from MySourceTable
to see if you get anything other than 01 - 12
0
 

Author Comment

by:TechNovation
ID: 24345863
I got 00 as well but it has to do with the original entry which is 00000000, so I can ignore the 00 and assume the rest is correct. shall I use substring([date filed],5,2) in the query then for each column of months?
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 24345895
yes, try that
0
 

Author Comment

by:TechNovation
ID: 24345944
Ok it works
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 24345948
cool,  but I had asked yesterday if "there is just a completely wrong value in that field, like all zeros or something".  we should have checked it then and saved some time. :)
0
 
LVL 6

Expert Comment

by:bokist
ID: 24346870
if date field is nvarchar - you can handle it without conversion :

case when substring([date field],4,2) = '01' then count(*) else 0 end) as Jan ... &  so on
 
substring part depend on date format in Date field  
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now