title: Changing integer to floating point and adding decimal point
body: <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>