Solved

ERROR: Arithmetic overflow error converting expression to data type datetime

Posted on 2009-05-08
30
911 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
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!

 
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
 

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

Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need return values from a stored procedure 8 64
Many to one in one row 2 48
SQL- GROUP BY 4 53
Creating a View from a CTE 15 49
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

738 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