CODEWITHSUNDEEP
sqlvbavbscriptado
vmg
vmg

Reputation: 10576

Excel & SQL: How to convert string to double in Select statement

I have a sheet in Excel. One column in it ("ID") contains strings with values: 1.01, 1.02, 1.03, 2.01, 3.01, 3.04 etc.

I want to get all rows in that sheet, where "ID" is >= some value using SQL statement and ADO connection (from VBScript code). So, I have two questions:

1) Can I convert ID's values to double in SQL? So, then I can compare values as floating point numbers and apply < and > conditions. 2) Can I use in select statement Macro from Excel workbook?

Upvotes: 1

Views: 20369

Answers (3)

onedaywhen
onedaywhen

Reputation: 57063

The CAST to FLOAT function is CDbl() e.g. 

SELECT CDbl(ID) AS ID_float 
  FROM [Sheet$];

Note DOUBLE is a synonym for FLOAT, hence the contraction Cast to Double

Upvotes: 4

vmg
vmg

Reputation: 10576

I found the solution: if I cast column to Integer, I'll have the same effect, so, I used CInt function: 

Select  *  from ["&strSheetName&"$]   where CInt(TestCaseId)  >= " & abs (strRow)

Upvotes: 0

Dunc
Dunc

Reputation: 8068

try 

WHERE CONVERT (ID, FLOAT) >= value

Upvotes: 0

Related Questions