Link to home
Start Free TrialLog in
Avatar of Rcreese38
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.
Avatar of Aneesh
Aneesh
Flag of Canada image

>(CAST(quantity as int))

(CAST(quantity as bigint))
Avatar of Rcreese38
Rcreese38

ASKER

I tried bigint and received the following
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(quantity, '0', ' ')),' ' , '0')  ) as INT)
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(quantity, '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.
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg 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
Received the following error

Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
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 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