Link to home
Start Free TrialLog in
Avatar of theartha
thearthaFlag for United States of America

asked on

Arithmetic overflow error converting nvarchar to data type numeric.

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.
Avatar of knightEknight
knightEknight
Flag of United States of America image

INSERT INTO B
(
LastUpdated,
LastUpdatedBy
)
SELECT
convert(varchar,TIMESTAMP,121) as ]TimeStamp],
'TEST'
FROM A
oops, you may want to convert(nvarchar instead of just varchar.  In theory either would work though.
also I got my bracket turned around, should be:

    as [TimeStamp]
actually, the alias is not really needed, so:

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

ASKER

@KnightEknight:

I am trying this query

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

Still same error.

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

Still same error.
sorry, try this:

INSERT INTO B
(
LastUpdated,
LastUpdatedBy
)
SELECT
ltrim(rtrim(convert(nvarchar,cast([TIMESTAMP] as datetime,121))),
'TEST'
FROM A
grr!  I think I left out a closing paren around the cast function - should be:

ltrim(rtrim(convert(nvarchar,cast([TIMESTAMP] as datetime),121))),
I got Incorrect syntax near ','.

@ line...ltrim(rtrim(convert(nvarchar,cast([TIMESTAMP] as datetime,121))),
see above.  :)
ltrim(rtrim(convert(nvarchar,cast([TIMESTAMP] as datetime),121))),

Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.
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)
wow .. I got the results....
how to use this my query?
I mean, I got today's date
results:

2011-04-25 14:29:01.223
TIMESTAMP is the name of your column, right?
... and it is also of type "timestamp" ?
typo error: my column is TransTimeStamp datatype nvarchar(27), my bad
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?
or are you just trying to get the current date and time into B?
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.
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.
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
INSERT INTO B
(
LastUpdated,
LastUpdatedBy
)
SELECT
TransTimeStamp,
'TEST'
FROM A

DataTypes:

Table.A

TransTimeStamp: nvarchar(27)

Table.B

lastUpdated : DateTime
INSERT INTO B
(
LastUpdated,
LastUpdatedBy
)
SELECT
convert(datetime,ltrim(rtrim(TransTimeStamp))),
'TEST'
FROM A
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'
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



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

same error
stand by, i got this now...
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
Still the same error:

so I ran

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

Still same error.
okay, let's break it down ... run these separately:

select substring(TransTimeStamp,1,10)
from A

select substring(replace(TransTimeStamp,'.',':'),12,11)
from A
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.
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.
I mean these two:

select substring(TransTimeStamp,1,10)
from A

select substring(replace(TransTimeStamp,'.',':'),12,11)
from A
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





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
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
aha!  I see the problem - there is a leading space on those timestamps, correct?
... 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
-- 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
be back in 30 mins
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

How about these?

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

select substring(replace(ltrim(TransTimeStamp),'.',':'),12,11)
from A
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
ASKER CERTIFIED SOLUTION
Avatar of knightEknight
knightEknight
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
WOW...you are amazing....finally it worked.


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


Thank you very much.
Thanks again.