ERROR: Arithmetic overflow error converting expression to data type datetime

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

TechNovationAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Chris LuttrellConnect With a Mentor Senior Database ArchitectCommented:
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
 
RiteshShahCommented:
use

datepart(m,[Date Field])

instead of
month([Date Field])
0
 
bokistCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
RiteshShahCommented:
>>if date field is nvarchar - you can handle it without conversion :<<

date field is not nvarchar, it clearly shows in error message.
0
 
bokistCommented:
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
 
RiteshShahCommented:
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
 
bokistCommented:
That's agreed !
0
 
TechNovationAuthor Commented:
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
 
RiteshShahCommented:
have you tested my code?
0
 
RiteshShahConnect With a Mentor Commented:
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
 
Chris LuttrellSenior Database ArchitectCommented:
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
 
Chris LuttrellSenior Database ArchitectCommented:
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
 
RiteshShahCommented:
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
 
RiteshShahCommented:
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
 
Chris LuttrellSenior Database ArchitectCommented:
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
 
RiteshShahCommented:
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
 
TechNovationAuthor Commented:
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
 
RiteshShahCommented:
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
 
TechNovationAuthor Commented:
It returns 0 for all cells expect the TotalYear
0
 
RiteshShahCommented:
BTW, do you want count or sum?
0
 
RiteshShahCommented:
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
 
TechNovationAuthor Commented:
I get error:
"'is0' is not a recognized built-in function name."
For this I need sum.
0
 
TechNovationAuthor Commented:
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
 
Chris LuttrellSenior Database ArchitectCommented:
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
 
TechNovationAuthor Commented:
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
 
TechNovationAuthor Commented:
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
 
Chris LuttrellSenior Database ArchitectCommented:
yes, try that
0
 
TechNovationAuthor Commented:
Ok it works
0
 
Chris LuttrellSenior Database ArchitectCommented:
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
 
bokistCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.