Databases
StackOverflow2013
Postsdbo
POChanging integer to floating point and adding decimal point
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POChanging integer to floating point and adding decimal point
    primarykey
    data
    text
    <p>I'm working with SQL on Microsoft SQL Server Management Studio 2008. </p> <p>In a nut shell, I'm receiving data on coordinates as <code>115949833</code> and I need it to output as <code>115.949833</code> because I need to be able to calculate the mileage between the latitude and longitude coordinates. Both the longitude and latitude values are saved as integers with no decimal places. I was trying to change it to a string so I could use a substring to concatenate and convert it into a floating point. I've already figured out the formula for the mileage between lat and long values but its not accurate because of the issue with the decimal point. </p> <p>Here's the code I used for the longitude: </p> <p><code>Cast(substring(str(longitude,1,not null),1,3)+'.'+substring(str(longitude,4,not null),4,9) as float) as longitude</code></p> <p>It keeps telling me I have an incorrect syntax near <code>CAST</code>. </p> <p>I've been doing a lot of research and still couldn't find an exact way of performing this task. If anyone could provide any feedback, tips, help, etc. It would be greatly appreciated. Thanks. </p> <p>***EDIT: I need the entire column named "Longitude" to be converted into a floating point or to somehow display a decimal after the 3rd character and the entire column named "Latitude" to display a decimal after the 2nd character. </p> <p>So for example right now my columns show: </p> <p>Latitude Longitude <br> 36158500 115949833<br> 36340000 115914667<br> 36153488 115944875<br> <p> and I need it to look like this: <p> Latitude Longitude <br> 36.158500 115.949833 <br> 36.340000 115.914667 <br> 36.153488 115.944875 <br></p> <p>*<strong><em>EDIT</em>*</strong> So using Tim Lehner's Answer, I've used my code and implemented his answer, but now I need to figure out how to get it to show the mileage using the new Latitude and Longitude columns... I created a few temp table's to store the information I wanted in based on specific radio_name's but I can't pull the info from those temp tables using Tim's Answer. </p> <p><b>Here's the code I'm using: </b><br> USE [system]<br> GO<br> With CTE as <br> (SELECT * FROM AVL <br> WHERE (DATE_TIME between '01/30/2013 00:00:00' AND <br> '01/30/2013 23:59:59') AND radio_name = 'MAS7') <br> CTE2 as <br> (select *,row_number() over(partition by Vehicle_ID order by Date_Time) as RN FROM CTE)<br></p> <pre><code> SELECT *, sqrt((69.1*(previous.Latitude - next.Latitude))* (69.1*(previous.Latitude-next.Latitude)) + (69.1*(previous.Longitude-next.Longitude)) * cos(next.Latitude/57.3) * (69.1*(previous.longitude-next.Longitude)) * cos(next.Latitude/57.3)) as Miles From CTE2 as Previous Join CTE2 as Next On previous.Vehicle_ID = Next.Vehicle_ID AND Previous.RN = Next.RN - 1 SELECT CAST(Latitude / 1000000.0 as decimal(10, 6)) as Latitude, cast(Longitude / 1000000.0 as decimal(10, 6)) as Longitude from AVL </code></pre> <p>The actual code itself does work and function properly but the mileage calculation is using the original whole latitude/longitude numbers and not the updated numbers that have the decimal point.<p></p> <p>I tried plugging in CTE2 in the last statement instead of AVL and it says it's an invalid object name. Any pointers or tips?... </p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USRisa Marie
    1. USRisa Marie
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POChanging integer to floating point and adding decimal point
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POChanging integer to floating point and adding decimal point
      1. USRisa Marie
      1. VTFavorite
    1. COYou have 5 left parentheses and 6 right ones, so even if nothing else is wrong that would cause an error.
      singulars
      1. POChanging integer to floating point and adding decimal point
      1. USPondlife
    2. COA CTE can only be used by the statement directly following it. You might consider creating a new question for this issue, and that of calculating distance.
      singulars
      1. POChanging integer to floating point and adding decimal point
      1. USTim Lehner
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload