Solved

ERROR: Arithmetic overflow error converting expression to data type datetime

Posted on 2009-05-08
30
913 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

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

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.

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

627 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