?
Solved

Arithmetic overflow error converting expression to data type smalldatetime.

Posted on 2005-03-08
8
Medium Priority
?
2,071 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
[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
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

752 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