[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 380
  • 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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