| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 40776 | 2003-12-18 00:25:00 | SQL query | Mike (15) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 201350 | 2003-12-18 00:25:00 | I have to make a SQL query on numerical records in a String field (don't ask why its a string field and not a Double field), but I want the results to be displayed rounded to two decimal places (many of the records are to 7 decimal places). Is this possible? PS I know very little aout SQL :D Mike. |
Mike (15) | ||
| 201351 | 2003-12-18 00:45:00 | I know very little SQL so I will leave that to people more qualified but the result could be formatted to two decimals like this where MyResult is your variable holding the number that has decimals. MyResult = Format(MyResult,"0.00") If the data type is string then your going to run into problems if you need to do arithmetic against it so you may have to convert to a number then back to a string again in these cases. There being a bit tough on you at work arent they? First making you go through thousands of names then asked to do this. :-) |
parry (27) | ||
| 201352 | 2003-12-18 01:55:00 | This is based on MS SQL Server 2000 select convert(numeric(7,1),substring(string_field,start, finish)) numeric_field from table_name where start and finish are the start and stop points in your string if you want the whole string field: convert(numeric(7,1),string_field) numeric_field |
Marlboro (4607) | ||
| 201353 | 2003-12-18 01:59:00 | Sorry, to get 2 decimal places, you'll need to change the convert(numeric(7,1) to convert(numeric(7,2) |
Marlboro (4607) | ||
| 201354 | 2003-12-18 02:00:00 | Can you give us a few more details Mike. Are you still using Excel? Do you have all of the output already displayed and just need to to show the 2 decimal places? I dont think you can do it with a query (although Im a bit rusty on my SQL). The only thing you could do would be to select the number of characters you wanted returned from the query given that you knew how long the returned string would be. For example if you knew that every string would be 5 characters long before the decimal point then you can write a query to return only the first 8 characters, i.e the first 5 numbers, the decimal point and then the next 2 numbers. Anyway give us some more info and we'll go from there. B. |
Barnabas (4562) | ||
| 201355 | 2003-12-18 02:46:00 | Hi, No I'm not using Excel for this - I'm using a map program called ArcMap, and I'm trying to label something using fields out of a SQL database. The scripty thing telling it how to create the label goes something like this: Function FindLabel ( [unittype], [zcoord], [mhdpth] ) FindLabel = [unittype] & vbnewline & [zcoord] & vbnewline & [zcoord]-[mhdpth] & vbnewline & [mhdpth] End Function "zcoord" is the number in a string field where I need it cut down/rounded to two decimal places. The number of places BEFORE the decimal will be between 1 and 3 (0-120 I think the range is), but after the decimal is all sorts of numbers, but should be a maximum of 2 decimal places. I'm lost with this :) Mike. |
Mike (15) | ||
| 201356 | 2003-12-18 03:41:00 | Are you saying that your function doesn't work? | Marlboro (4607) | ||
| 201357 | 2003-12-18 03:57:00 | > Are you saying that your function doesn't work? No, my function works fine ?:| My problem is that it displays "zcoord" with anywhere from 0 to 7 decimal places, and I want it shown to 2 decimal places. It's fine with all the other fields as they're numeric fields, but zcoord is a string. I cannot change it from being string to numeric, I just need to display what's contained in the string to 2 decimal places. This is just for display purposes - no editing of the database is taking place, nor can it take place. Mike. |
Mike (15) | ||
| 201358 | 2003-12-18 03:59:00 | try replacing occurances of zcoord with convert(numeric(3,1),zcoord) |
Marlboro (4607) | ||
| 201359 | 2003-12-18 04:10:00 | And you know your latitude and longitude to within .001 sec (2 mm), Mike? That's some precision. :D | Graham L (2) | ||
| 1 2 | |||||