• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 382
  • Last Modified:

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.
0
Rcreese38
Asked:
Rcreese38
  • 5
  • 2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
>(CAST(quantity as int))

(CAST(quantity as bigint))
0
 
Rcreese38Author Commented:
I tried bigint and received the following
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
and quantity = CAST(CONVERT(numeric(20,3), REPLACE(LTRIM(REPLACE(quantity, '0', ' ')),' ' , '0')  ) as INT)
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
Rcreese38Author Commented:
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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry, but the code on the wrong place:

select item_number, CAST(CONVERT(numeric(25,5), REPLACE(LTRIM(REPLACE(quantity, '0', '')),'' , '0')  ) as INT) quantity
FROM copy_t_macola_interface
where written_date between '7/30/07' and '7/31/07' -- and quantity =
GROUP BY item_number, quantity
ORDER BY item_number
0
 
Rcreese38Author Commented:
Received the following error

Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
0
 
Rcreese38Author Commented:
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
0
 
Rcreese38Author Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now