Solved

Coldfusion varchar to integer

Posted on 2007-03-28
2
715 Views
Last Modified: 2010-08-05
I am trying to convert a datatype of varchar to interger. The reason is I need the string to work using the sql between statement. I'm extracting results from a database and creating a total for each range. See code below

<cfparam name="sortby" default="Pub_Detail_Order_Date">
<cfif isDefined("url.sortby")>
      <cfset sortby = url.sortby>
</cfif>
<cfparam name="direction" default="DESC">
<cfif isDefined("url.direction")>
      <cfset direction = url.direction>
</cfif>
<cfquery datasource="#application.dsName#" username="#application.dsUserName#" password="#application.dsPassword#" name="forms">
SELECT *
FROM Pub_Details
            WHERE Pub_Detail_Title LIKE '%IMPRINTED - %'
                  Order BY #sortby# #direction#

</cfquery>

<cfquery dbtype="query" name="first_qry">
     SELECT * FROM forms WHERE Pub_Detail_Qty >= '0' and Pub_Detail_Qty <= '999'
</cfquery>
<cfquery dbtype="query" name="second_qry">
     SELECT * FROM forms WHERE Pub_Detail_Qty >= '1000' and Pub_Detail_Qty <= '1999'
</cfquery>
<cfquery dbtype="query" name="third_qry">
     SELECT * FROM forms WHERE Pub_Detail_Qty >= '2000'
</cfquery>

I get great results, except that I don't only get numbers 0 - 999 in the first query. 1500 shows up too. I am using MSSQL. The following code is what gets generated for the first query of a query:

Imprinting and Blank Orders Between 0 and 999
Date Qty Unit Cost Title Extended Cost
27-Jan-06 12:28 PM
 35
 .45
 IMPRINTED - PLUS MPN
 $15.75
 
27-Jan-06 05:23 PM
 250
 .45
 IMPRINTED - PLUS MPN
 $112.5
 
27-Jan-06 05:23 PM
 500
 .45
 IMPRINTED - PLUS SCHOOL CERTIFICATION
 $225
 
19-Oct-06 01:38 PM
 500
 .45
 IMPRINTED - STAFFORD MPN
 $225
 
19-Oct-06 01:31 PM
 500
 .45
 IMPRINTED - STAFFORD MPN
 $225
 
18-Jan-06 11:04 PM
 222
 .45
 IMPRINTED - PLUS MPN
 $99.9
 
18-Jan-06 11:03 PM
 222
 .45
 IMPRINTED - PLUS MPN
 $99.9
 
18-Jan-06 10:43 PM
 222
 .45
 IMPRINTED - PLUS MPN
 $99.9
 
18-Jan-06 10:24 PM
 150
 .45
 IMPRINTED - PLUS MPN
 $67.5
 
18-Jan-06 10:24 PM
 1500
 .45
 IMPRINTED - PLUS SCHOOL CERTIFICATION
 $675
 
02-Aug-06 12:27 PM
 5
 .45
 IMPRINTED - PLUS SCHOOL CERTIFICATION
 $2.25
 
0
Comment
Question by:asaworker
2 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 18810962
do NOT use quotes around numerical data/fields:

     SELECT * FROM forms WHERE Pub_Detail_Qty >= 0 and Pub_Detail_Qty <= 999

     SELECT * FROM forms WHERE Pub_Detail_Qty >=  1000 and Pub_Detail_Qty <=  1999

etc

hopefully, the field Pub_Details_Qty is of numerical data type, otherwise you should change that.
0
 

Author Comment

by:asaworker
ID: 18811153
Thanks angellll, I figured that might of been the root of the problem.
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question