SQL Arithmetic overflow when displaying date variable

WHY is this so hard for me to grasp.  I can use an SQL select statement to display a variable by simply using

     ,cast('2013-01-01' as date)

However, if I set that exact syntax to a variable

     DECLARE @startdate Date = cast('2013-01-01' as date)

then try to display it in a select statment as

     ,@startdate

I get "Error: Arithmetic overflow error converting varchar to data type numeric"

I never used numeric.  What gives??
LVL 1
sifugregAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sifugregAuthor Commented:
Oh how very interesting.  Apparently it has something to do with SQL's ability to interpret variables.  I really dummied down the query to set a variable to a varchar and the 'test' and got the same results.  I discovered that if I went through the rest of the query placing all numerical values in quotes the query ran fine.  It seems to be similar to setting "option explicit" when you define a variable....
0
Anoo S PillaiCommented:
Is the error related the cast you have posted ?  The following is working perfectly fine for me.

 DECLARE @startdate Date = cast('2013-01-01' as date)
 SELECT  @startdate

I believe, something related to the remaining columns in the select is causing that conversion error. Could you post the code that you are trying.
0
PortletPaulfreelancerCommented:
i ageee with Anoo, there is no syntax issue in the SQL portion supplied, so the check conversion error is probably elsewhere. It may be an implict conversion so it may not necessarily  involve an explicit cast/convert.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Agreed.  Show us your code, as the below works fine on my SSMS
DECLARE @startdate  Date = cast('2013-01-01' as date)
SELECT @startdate

Open in new window

0
Scott PletcherSenior DBACommented:
I really dummied down the query to set a variable to a varchar and the 'test' and got the same results.  I discovered that if I went through the rest of the query placing all numerical values in quotes the query ran fine.  It seems to be similar to setting "option explicit" when you define a variable....

No, that is intentional behavior.

SQL had to decide ahead of time what is the result of expressions like:
'1' + 2
would be, 3 or '12'?  SQL decided to make it 3, i.e., that numeric values have precedence over varchar ones.

Of course, when you write:
'A' + 2
you wish SQL were "smart" enough to "realize" that this not really a numeric expression, but SQL can't do that.  It has to have specific rules.  Therefore, the code above results in an error, since SQL tries to convert the 'A' to a numeric value.  Whereas obviously this code would not cause an error:
'A' + '2'
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sifugregAuthor Commented:
Yeah, this one was kinda wild (at least in my mind) much lower in the SQL there was a "where" condition looking for obsvalue > 8.0 which worked perfectly until I used that variable to look for a completely different column in the same table [obsdate] then I got that error.  Locatin the 8.0 and placing it in quoted fixed it all.  How that specifically relates to establishing a datetime variable is still beyond me but clearly it changes how SQL looks at the code and tightens up the constraints.
0
sifugregAuthor Commented:
Scott, that makes perfect sense.  I'm actually surprised it worked in the first place and still a little amazed that it is capable of making the correct decision before a completely unrelated variable is defined but obviously it is best practice to identify the variable type and treat them accordingly.  This was an old query I was modifying which is why I missed it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.