Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ERROR: Arithmetic overflow error converting expression to data type datetime

Posted on 2009-05-08
30
Medium Priority
?
916 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 400 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 27

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 27

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 27

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
 

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 27

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 27

Accepted Solution

by:
Chris Luttrell earned 1600 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 27

Expert Comment

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

Author Comment

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

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

971 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