CODEWITHSUNDEEP
sqlsql-serversql-server-2005sql-server-2012
StealthRT
StealthRT

Reputation: 10552

SQL query in 2012 works, 2005 does not

I have the following query that i need help with converting it to syntax that MS SQL 2005 would understand since FORMAT among other things in it are not supported by that old version.

"SELECT " & _
     "TMP.*," & _
     "COUNT(*) OVER () AS rCount " & _
"FROM (" & _
     "SELECT venueID, " & _
         "venueName AS venueName, " & _
         "venueAddress + ', ' + venueCity + ', ' + venueState + ' ' + venueZip AS venueAddress, " & _
         "venueLatLong AS coordinates, " & _
         "FORMAT(venueEventDate, 'MM/dd/yyyy', 'en-US') + ' @ ' + CONVERT(VARCHAR,venueTime) AS dateAndTime, " & _
         "SUBSTRING(venueLatLong, 1, CHARINDEX(',', venueLatLong)-1) AS Lat, " & _
         "SUBSTRING(venueLatLong, CHARINDEX(',', venueLatLong) + 1, 1000) AS Lng, " & _
         "(round(" & _
             "3959 * acos " & _
               "(" & _
                   "cos(radians('" & center_lat & "')) " & _
                   "* cos(radians(SUBSTRING(venueLatLong, 1, CHARINDEX(',', venueLatLong)-1))) " & _
                   "* cos(radians(SUBSTRING(venueLatLong, CHARINDEX(',', venueLatLong) + 1, 1000)) " & _
                   "- radians('" & center_lng & "')) " & _
                   "+ sin(radians('" & center_lat & "')) " & _
                   "* sin(radians(SUBSTRING(venueLatLong, 1, CHARINDEX(',', venueLatLong)-1)))" & _
               ")" & _
        ", 1, 1)) AS distance " & _
        "FROM meetUpMarkers) " & _
     "TMP " & _
"WHERE distance < " & radius & " " & _
"ORDER BY venueName,distance DESC;"

I tried to replace FORMAT with CONVERT but it still seems to be incorrect.

When i change FORMAT to CONVERT i get the error:

Type venueEventDate is not a defined system type.

UPDATE

Uggg... now when I run it local i get this:

Now even when i run it local i get this error???!??! and its 2012:

Msg 8114, Level 16, State 5, Line 2 Error converting data type varchar to float.

Does this mean line 2, word 5 has the error?

Would appreciate the help.

Upvotes: 0

Views: 99

Answers (1)

Bacon Bits
Bacon Bits

Reputation: 32180

Arguments for FORMAT() and CONVERT() are in a different order. The error message indicates you put the field name into the data type argument position. It's:

FORMAT ( value, format [, culture ] )

vs

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Try replacing FORMAT(venueEventDate, 'MM/dd/yyyy', 'en-US') with this:

CONVERT(VARCHAR, venueEventDate, 101)`

Upvotes: 1

Related Questions