We help IT Professionals succeed at work.

Arithmetic overflow error converting nvarchar to data type numeric.

4,513 Views
Last Modified: 2012-05-11
INSERT INTO B
(
LastUpdated,
LastUpdatedBy
)
SELECT
TIMESTAMP,
'TEST'
FROM A


I am trying to insert into B from A. I got the following error message.

Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting nvarchar to data type numeric.
The statement has been terminated.

DataTypes:

Table.B

lastUpdated : nvarchar(27)

Table.A

TimeStamp: DateTime

I am using SQL Server 2005

Please advice.

Thanks.
Comment
Watch Question

CERTIFIED EXPERT

Commented:
INSERT INTO B
(
LastUpdated,
LastUpdatedBy
)
SELECT
convert(varchar,TIMESTAMP,121) as ]TimeStamp],
'TEST'
FROM A
CERTIFIED EXPERT

Commented:
oops, you may want to convert(nvarchar instead of just varchar.  In theory either would work though.
CERTIFIED EXPERT

Commented:
also I got my bracket turned around, should be:

    as [TimeStamp]
CERTIFIED EXPERT

Commented:
actually, the alias is not really needed, so:

INSERT INTO B
(
LastUpdated,
LastUpdatedBy
)
SELECT
convert(nvarchar,TIMESTAMP,121),
'TEST'
FROM A

Author

Commented:
@KnightEknight:

I am trying this query

INSERT INTO B
(
LastUpdated,
LastUpdatedBy
)
SELECT
ltrim(rtrim(convert(nvarchar,TIMESTAMP,121))),
'TEST'
FROM A

Still same error.

Author

Commented:

INSERT INTO B
(
LastUpdated,
LastUpdatedBy
)
SELECT
convert(nvarchar,TIMESTAMP,121),
'TEST'
FROM A

Still same error.
CERTIFIED EXPERT

Commented:
sorry, try this:

INSERT INTO B
(
LastUpdated,
LastUpdatedBy
)
SELECT
ltrim(rtrim(convert(nvarchar,cast([TIMESTAMP] as datetime,121))),
'TEST'
FROM A
CERTIFIED EXPERT

Commented:
grr!  I think I left out a closing paren around the cast function - should be:

ltrim(rtrim(convert(nvarchar,cast([TIMESTAMP] as datetime),121))),

Author

Commented:
I got Incorrect syntax near ','.

@ line...ltrim(rtrim(convert(nvarchar,cast([TIMESTAMP] as datetime,121))),
CERTIFIED EXPERT

Commented:
see above.  :)

Author

Commented:
ltrim(rtrim(convert(nvarchar,cast([TIMESTAMP] as datetime),121))),

Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.
CERTIFIED EXPERT

Commented:
hmm, now that works for me.  Try this and tell me the result you get:

declare @ts timestamp
select  @ts = convert(timestamp,GETDATE())  -- initialize @ts to a timestamp
select CONVERT(nvarchar,cast(@ts as datetime),121)

Author

Commented:
wow .. I got the results....
how to use this my query?

Author

Commented:
I mean, I got today's date

Author

Commented:
results:

2011-04-25 14:29:01.223
CERTIFIED EXPERT

Commented:
TIMESTAMP is the name of your column, right?
CERTIFIED EXPERT

Commented:
... and it is also of type "timestamp" ?

Author

Commented:
typo error: my column is TransTimeStamp datatype nvarchar(27), my bad
CERTIFIED EXPERT

Commented:
I understood that you were not using the real table/column names, so no worries - I just wanted to make sure about the types.  The data type of TransTimeStamp on table B is nvarchar, but what is it on table A - timestamp or datetime?
CERTIFIED EXPERT

Commented:
or are you just trying to get the current date and time into B?

Author

Commented:
declare @ts TRANSTIMESTAMP
select  @ts = convert(TRANSTIMESTAMP,GETDATE())  -- initialize @ts to a timestamp
select CONVERT(nvarchar,cast(@ts as datetime),121) from IMPORT_WMFUNT

Msg 2715, Level 16, State 3, Line 3
Column, parameter, or variable #1: Cannot find data type TRANSTIMESTAMP.
Parameter or variable '@ts' has an invalid data type.
CERTIFIED EXPERT

Commented:
Yes, TIMESTAMP is a valid data type, as is DATETIME ... I would expect TransTimeStamp to be your column name, but I need to know what its type is.
CERTIFIED EXPERT

Commented:
are you just trying to get the current date and time into B?  If so, then:

INSERT INTO B
(
LastUpdated,
LastUpdatedBy
)
SELECT
ltrim(rtrim(convert(nvarchar,getdate(),121))),
'TEST'
FROM A

Author

Commented:
INSERT INTO B
(
LastUpdated,
LastUpdatedBy
)
SELECT
TransTimeStamp,
'TEST'
FROM A

DataTypes:

Table.A

TransTimeStamp: nvarchar(27)

Table.B

lastUpdated : DateTime
CERTIFIED EXPERT

Commented:
INSERT INTO B
(
LastUpdated,
LastUpdatedBy
)
SELECT
convert(datetime,ltrim(rtrim(TransTimeStamp))),
'TEST'
FROM A
CERTIFIED EXPERT

Commented:
now, the above assumes that TransTimeStamp has values in it that are properly formatted dates, like '04/25/2011' or '2011-04-25 03:14:01'

Author

Commented:
2010-10-18-09.34.14.000000 is nvarchar value in Table A.

I got the same error Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.

So I ran this query

select CONVERT(nvarchar,cast([TRANSTIMESTAMP] as datetime),121) from A

Still the same error



Author

Commented:
select convert(datetime,ltrim(rtrim(TransTimeStamp))) from A

same error
CERTIFIED EXPERT

Commented:
stand by, i got this now...
CERTIFIED EXPERT

Commented:
The date format of that column is non-standard, so first we have to transform it into a standard date, which is why this query is so ugly, but (hopefully) it will work:


INSERT INTO B
(
 LastUpdated,
 LastUpdatedBy
)
SELECT
convert(datetime,substring(TransTimeStamp,1,10)+' '+substring(replace(TransTimeStamp,'.',':'),12,11)),
'TEST'
FROM A

Author

Commented:
Still the same error:

so I ran

select convert(datetime,substring(TransTimeStamp,1,10)+' '+substring(replace(TransTimeStamp,'.',':'),12,11))
 from A

Still same error.
CERTIFIED EXPERT

Commented:
okay, let's break it down ... run these separately:

select substring(TransTimeStamp,1,10)
from A

select substring(replace(TransTimeStamp,'.',':'),12,11)
from A

Author

Commented:
When I tried to convert the datatype, by right click and modify:

'A' table
- Warning: Data might be lost converting column 'TransTimestamp' from 'nvarchar(27)'.

I clicked Yes

Got the following message

'A' table
- Unable to modify table.  
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.
CERTIFIED EXPERT

Commented:
I don't expect that will work anyway because the format of the text in that column is not a standardized date format.

Please post the results of the 2 queries above.
CERTIFIED EXPERT

Commented:
I mean these two:

select substring(TransTimeStamp,1,10)
from A

select substring(replace(TransTimeStamp,'.',':'),12,11)
from A

Author

Commented:
Query #1:

select substring(TransTimeStamp,1,10)
from A

Results:

 2010-10-1
 2010-10-1
 2010-10-1
 2010-10-1

Query #2:

select substring(replace(TransTimeStamp,'.',':'),12,11)
from A

Results:

-09:34:14:0
-09:34:29:0
-09:34:45:0
-09:35:02:0





Author

Commented:
Query #3:


select TransTimeStamp from A


Results:

 2010-10-18-09.34.14.000000
 2010-10-18-09.34.29.000000
 2010-10-18-09.34.45.000000
 2010-10-18-09.35.02.000000
CERTIFIED EXPERT

Commented:
well, it doesn't look like that on my system, but maybe we can correct for it like this ... please post the results:

select substring(TransTimeStamp,1,11)
from A

select substring(replace(TransTimeStamp,'.',':'),13,8)
from A
CERTIFIED EXPERT

Commented:
aha!  I see the problem - there is a leading space on those timestamps, correct?
CERTIFIED EXPERT

Commented:
... that's why you were doing the ltrim / rtrim thing ... let's try this (only ltrim is necessary):

select substring(ltrim(TransTimeStamp),1,10)
from A

select substring(replace(ltrim(TransTimeStamp),'.',':'),12,11)
from A
CERTIFIED EXPERT

Commented:
-- and if that works, then hopefully this will too:

INSERT INTO B
(
 LastUpdated,
 LastUpdatedBy
)
SELECT
convert(datetime,substring(ltrim(TransTimeStamp),1,10)+' '+substring(replace(ltrim(TransTimeStamp),'.',':'),12,11)),
'TEST'
FROM A
CERTIFIED EXPERT

Commented:
be back in 30 mins

Author

Commented:
yes.

(Query 1)
select substring(TransTimeStamp,1,11)
from A
Results:
 2010-10-18
 2010-10-18
 2010-10-18
 2010-10-18

(Query 1)
select substring(replace(TransTimeStamp,'.',':'),13,8)
from A

Results
09:34:14:000000
09:34:29:000000
09:34:45:000000

Query 3


Results:
 2010-10-18-09.34.14.000000
 2010-10-18-09.34.29.000000
 2010-10-18-09.34.45.000000
 2010-10-18-09.35.02.000000

CERTIFIED EXPERT

Commented:
How about these?

select substring(ltrim(TransTimeStamp),1,10)
from A

select substring(replace(ltrim(TransTimeStamp),'.',':'),12,11)
from A

Author

Commented:
select substring(ltrim(TransTimeStamp),1,10)
from A

2010-10-18
2010-10-18
2010-10-18
2010-10-18

select substring(replace(ltrim(TransTimeStamp),'.',':'),12,11)
from A
09:34:14:00
09:34:29:00
09:34:45:00
09:35:02:00
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
WOW...you are amazing....finally it worked.


Thank you very much for the efforts. You made my day.


Thank you very much.

Author

Commented:
Thanks again.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.