?
Solved

Arithmetic overflow error converting expression to data type smalldatetime.

Posted on 2005-03-08
8
Medium Priority
?
2,093 Views
Last Modified: 2007-12-19
i get this error while converting a nvarchar to string how do i resolve it.
Arithmetic overflow error converting expression to data type smalldatetime.
Thanks
SQl SErver 2000

Ashish
0
Comment
Question by:AsNanda
8 Comments
 
LVL 15

Expert Comment

by:mcmonap
ID: 13491340
Hi AsNanda,

What is the value you are trying to convert?
0
 
LVL 66

Accepted Solution

by:
Jim Horn earned 140 total points
ID: 13492769
Overflow usually means you are trying to insert a value that is longer than the field can handle, for example "55123-1234" into a field with data type char(5).  I'd eyeball your original field and make sure there are no values that would go over the # in nvarchar(#) you're trying to move them to.
0
 
LVL 1

Expert Comment

by:rcole02
ID: 13496305
The value to smalldatetime is what the server is trying to convert your nvarchar to. Dates are really just long numbers representing days since 0000. What is in the variable you are trying to convert?

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

 

Author Comment

by:AsNanda
ID: 13497270
The value i have originaly is a date in excel , i have to convert that in to a csv file becasue of the data import errors.
the column has 90% null values but when i try to import it in SQL it gives me an error sayin that the cant convert a char to date but my excel has it as date, Well so finaly i created this csv file and then imported data. My column is a nvarchar and i am trying to create a view and change it back to the date value for outpput.
Does this makes sense.
thanks

Ashish
0
 
LVL 15

Expert Comment

by:mcmonap
ID: 13497341
Hi AsNanda,

Yes it does BUT can you provide a sample of the data in the column?
0
 

Author Comment

by:AsNanda
ID: 13499408
Initial Excel file : 01/01/2005
CSV File : 01/01/2005
SQL Server Table : 01/01/2005
but when i create the view .. it gives me that error .
Thanks
0
 
LVL 15

Assisted Solution

by:mcmonap
mcmonap earned 160 total points
ID: 13503968
Hi AsNanda,

If if can export from excel into csv in this format:yyyymmdd then you should be able to import them directly into SQL without a problem.  You can convert them in the worksheet with a function something like below (there may be better ways to do this):
=YEAR(A1)&IF(MONTH(A1)<10, "0"&MONTH(A1))&IF(DAY(A1)<10, "0"&DAY(A1))
0
 
LVL 15

Expert Comment

by:mcmonap
ID: 13503972
I meant to add doing like this you should be able to import into a datetime column in SQL rather than a nvarchar which will save converting it for the view.
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

Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

571 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