Rcreese38
asked on
SQL Server 2000 Date Conversion Issue
I am having an issue converting a data type in a SQL Server 2000 Query. Here is the Query I am using
USE "HJ t_macola_interface"
GO
select item_number, (CAST(quantity as int))
FROM copy_t_macola_interface
where written_date between '7/30/07' and '7/31/07' --and quantity = CONVERT(numeric, quantity)
GROUP BY item_number, quantity
ORDER BY item_number
GO
The Error I Receive is
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value '000000000130.00' to a column of data type int.
USE "HJ t_macola_interface"
GO
select item_number, (CAST(quantity as int))
FROM copy_t_macola_interface
where written_date between '7/30/07' and '7/31/07' --and quantity = CONVERT(numeric, quantity)
GROUP BY item_number, quantity
ORDER BY item_number
GO
The Error I Receive is
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value '000000000130.00' to a column of data type int.
ASKER
I tried bigint and received the following
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.
and quantity = CAST(CONVERT(numeric(20,3) , REPLACE(LTRIM(REPLACE(quan tity, '0', ' ')),' ' , '0') ) as INT)
ASKER
Tried this and received the following error
USE "HJ t_macola_interface"
GO
select item_number, (CAST(quantity as int))
FROM copy_t_macola_interface
where written_date between '7/30/07' and '7/31/07' and quantity = CAST(CONVERT(numeric(25,5) , REPLACE(LTRIM(REPLACE(quan tity, '0', '')),'' , '0') ) as INT)
GROUP BY item_number, quantity
ORDER BY item_number
GO
Error
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value '000000000216.00' to a column of data type int.
USE "HJ t_macola_interface"
GO
select item_number, (CAST(quantity as int))
FROM copy_t_macola_interface
where written_date between '7/30/07' and '7/31/07' and quantity = CAST(CONVERT(numeric(25,5)
GROUP BY item_number, quantity
ORDER BY item_number
GO
Error
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value '000000000216.00' to a column of data type int.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Received the following error
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
ASKER
This looks like the biggest number I have 000000015750.00
I used TOP 10 and received 10 results, Not sure how to expand the number
I used TOP 10 and received 10 results, Not sure how to expand the number
ASKER
I don't think I can use the zero as my select becuase. Because I have number like
000000000130.00 that are being retured as 13
000000000130.00 that are being retured as 13
(CAST(quantity as bigint))