Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2329
  • Last Modified:

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.
0
theartha
Asked:
theartha
  • 28
  • 20
1 Solution
 
knightEknightCommented:
INSERT INTO B
(
LastUpdated,
LastUpdatedBy
)
SELECT
convert(varchar,TIMESTAMP,121) as ]TimeStamp],
'TEST'
FROM A
0
 
knightEknightCommented:
oops, you may want to convert(nvarchar instead of just varchar.  In theory either would work though.
0
 
knightEknightCommented:
also I got my bracket turned around, should be:

    as [TimeStamp]
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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

INSERT INTO B
(
LastUpdated,
LastUpdatedBy
)
SELECT
convert(nvarchar,TIMESTAMP,121),
'TEST'
FROM A
0
 
thearthaAuthor 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.
0
 
thearthaAuthor Commented:

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

Still same error.
0
 
knightEknightCommented:
sorry, try this:

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

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

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

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

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

DataTypes:

Table.A

TransTimeStamp: nvarchar(27)

Table.B

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



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

same error
0
 
knightEknightCommented:
stand by, i got this now...
0
 
knightEknightCommented:
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
0
 
thearthaAuthor 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.
0
 
knightEknightCommented:
okay, let's break it down ... run these separately:

select substring(TransTimeStamp,1,10)
from A

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

select substring(TransTimeStamp,1,10)
from A

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





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

0
 
knightEknightCommented:
How about these?

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

select substring(replace(ltrim(TransTimeStamp),'.',':'),12,11)
from A
0
 
thearthaAuthor 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
0
 
knightEknightCommented:
okay, here we go:

INSERT INTO B
(
 LastUpdated,
 LastUpdatedBy
)
SELECT
convert(datetime,substring(ltrim(TransTimeStamp),1,10)+' '+substring(replace(ltrim(TransTimeStamp),'.',':'),12,8)),
'TEST'
FROM A
0
 
thearthaAuthor Commented:
WOW...you are amazing....finally it worked.


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


Thank you very much.
0
 
thearthaAuthor Commented:
Thanks again.
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 28
  • 20
Tackle projects and never again get stuck behind a technical roadblock.
Join Now