theartha
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.
(
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.
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]
as [TimeStamp]
actually, the alias is not really needed, so:
INSERT INTO B
(
LastUpdated,
LastUpdatedBy
)
SELECT
convert(nvarchar,TIMESTAMP ,121),
'TEST'
FROM A
INSERT INTO B
(
LastUpdated,
LastUpdatedBy
)
SELECT
convert(nvarchar,TIMESTAMP
'TEST'
FROM A
ASKER
@KnightEknight:
I am trying this query
INSERT INTO B
(
LastUpdated,
LastUpdatedBy
)
SELECT
ltrim(rtrim(convert(nvarch ar,TIMESTA MP,121))),
'TEST'
FROM A
Still same error.
I am trying this query
INSERT INTO B
(
LastUpdated,
LastUpdatedBy
)
SELECT
ltrim(rtrim(convert(nvarch
'TEST'
FROM A
Still same error.
ASKER
INSERT INTO B
(
LastUpdated,
LastUpdatedBy
)
SELECT
convert(nvarchar,TIMESTAMP
'TEST'
FROM A
Still same error.
sorry, try this:
INSERT INTO B
(
LastUpdated,
LastUpdatedBy
)
SELECT
ltrim(rtrim(convert(nvarch ar,cast([T IMESTAMP] as datetime,121))),
'TEST'
FROM A
INSERT INTO B
(
LastUpdated,
LastUpdatedBy
)
SELECT
ltrim(rtrim(convert(nvarch
'TEST'
FROM A
grr! I think I left out a closing paren around the cast function - should be:
ltrim(rtrim(convert(nvarch ar,cast([T IMESTAMP] as datetime),121))),
ltrim(rtrim(convert(nvarch
ASKER
I got Incorrect syntax near ','.
@ line...ltrim(rtrim(convert (nvarchar, cast([TIME STAMP] as datetime,121))),
@ line...ltrim(rtrim(convert
see above. :)
ASKER
ltrim(rtrim(convert(nvarch ar,cast([T IMESTAMP] as datetime),121))),
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.
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)
declare @ts timestamp
select @ts = convert(timestamp,GETDATE(
select CONVERT(nvarchar,cast(@ts as datetime),121)
ASKER
wow .. I got the results....
how to use this my query?
how to use this my query?
ASKER
I mean, I got today's date
ASKER
results:
2011-04-25 14:29:01.223
2011-04-25 14:29:01.223
TIMESTAMP is the name of your column, right?
... and it is also of type "timestamp" ?
ASKER
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?
ASKER
declare @ts TRANSTIMESTAMP
select @ts = convert(TRANSTIMESTAMP,GET DATE()) -- 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.
select @ts = convert(TRANSTIMESTAMP,GET
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(nvarch ar,getdate (),121))),
'TEST'
FROM A
INSERT INTO B
(
LastUpdated,
LastUpdatedBy
)
SELECT
ltrim(rtrim(convert(nvarch
'TEST'
FROM A
ASKER
INSERT INTO B
(
LastUpdated,
LastUpdatedBy
)
SELECT
TransTimeStamp,
'TEST'
FROM A
DataTypes:
Table.A
TransTimeStamp: nvarchar(27)
Table.B
lastUpdated : DateTime
(
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(rtr im(TransTi meStamp))) ,
'TEST'
FROM A
(
LastUpdated,
LastUpdatedBy
)
SELECT
convert(datetime,ltrim(rtr
'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'
ASKER
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([TRA NSTIMESTAM P] as datetime),121) from A
Still the same error
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([TRA
Still the same error
ASKER
select convert(datetime,ltrim(rtr im(TransTi meStamp))) from A
same error
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 (TransTime Stamp,1,10 )+' '+substring(replace(TransT imeStamp,' .',':'),12 ,11)),
'TEST'
FROM A
INSERT INTO B
(
LastUpdated,
LastUpdatedBy
)
SELECT
convert(datetime,substring
'TEST'
FROM A
ASKER
Still the same error:
so I ran
select convert(datetime,substring (TransTime Stamp,1,10 )+' '+substring(replace(TransT imeStamp,' .',':'),12 ,11))
from A
Still same error.
so I ran
select convert(datetime,substring
from A
Still same error.
okay, let's break it down ... run these separately:
select substring(TransTimeStamp,1 ,10)
from A
select substring(replace(TransTim eStamp,'.' ,':'),12,1 1)
from A
select substring(TransTimeStamp,1
from A
select substring(replace(TransTim
from A
ASKER
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.
'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.
Please post the results of the 2 queries above.
I mean these two:
select substring(TransTimeStamp,1 ,10)
from A
select substring(replace(TransTim eStamp,'.' ,':'),12,1 1)
from A
select substring(TransTimeStamp,1
from A
select substring(replace(TransTim
from A
ASKER
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(TransTim eStamp,'.' ,':'),12,1 1)
from A
Results:
-09:34:14:0
-09:34:29:0
-09:34:45:0
-09:35:02:0
select substring(TransTimeStamp,1
from A
Results:
2010-10-1
2010-10-1
2010-10-1
2010-10-1
Query #2:
select substring(replace(TransTim
from A
Results:
-09:34:14:0
-09:34:29:0
-09:34:45:0
-09:35:02:0
ASKER
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
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(TransTim eStamp,'.' ,':'),13,8 )
from A
select substring(TransTimeStamp,1
from A
select substring(replace(TransTim
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(TransTimeS tamp),1,10 )
from A
select substring(replace(ltrim(Tr ansTimeSta mp),'.',': '),12,11)
from A
select substring(ltrim(TransTimeS
from A
select substring(replace(ltrim(Tr
from A
-- and if that works, then hopefully this will too:
INSERT INTO B
(
LastUpdated,
LastUpdatedBy
)
SELECT
convert(datetime,substring (ltrim(Tra nsTimeStam p),1,10)+' '+substring(replace(ltrim( TransTimeS tamp),'.', ':'),12,11 )),
'TEST'
FROM A
INSERT INTO B
(
LastUpdated,
LastUpdatedBy
)
SELECT
convert(datetime,substring
'TEST'
FROM A
be back in 30 mins
ASKER
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(TransTim eStamp,'.' ,':'),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
(Query 1)
select substring(TransTimeStamp,1
from A
Results:
2010-10-18
2010-10-18
2010-10-18
2010-10-18
(Query 1)
select substring(replace(TransTim
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(TransTimeS tamp),1,10 )
from A
select substring(replace(ltrim(Tr ansTimeSta mp),'.',': '),12,11)
from A
select substring(ltrim(TransTimeS
from A
select substring(replace(ltrim(Tr
from A
ASKER
select substring(ltrim(TransTimeS tamp),1,10 )
from A
2010-10-18
2010-10-18
2010-10-18
2010-10-18
select substring(replace(ltrim(Tr ansTimeSta mp),'.',': '),12,11)
from A
09:34:14:00
09:34:29:00
09:34:45:00
09:35:02:00
from A
2010-10-18
2010-10-18
2010-10-18
2010-10-18
select substring(replace(ltrim(Tr
from A
09:34:14:00
09:34:29:00
09:34:45:00
09:35:02:00
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
WOW...you are amazing....finally it worked.
Thank you very much for the efforts. You made my day.
Thank you very much.
Thank you very much for the efforts. You made my day.
Thank you very much.
ASKER
Thanks again.
(
LastUpdated,
LastUpdatedBy
)
SELECT
convert(varchar,TIMESTAMP,
'TEST'
FROM A