Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2003
    Posts
    59
    Provided Answers: 1

    Exclamation Unanswered: 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.
    Code:
    "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.

    Would appreciate the help.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Well, I did not know about the FORMAT function in SQL 2012. Might be nice to remember, although most formatting should really be done by the application layer that is closest to the user (with the database being firmly "in the rear with the gear"). You will want to use the CONVERT function as you already found out, the syntax should be replacing
    Code:
    "FORMAT(venueEventDate, 'MM/dd/yyyy', 'en-US')
    with
    Code:
    "CONVERT(venueEventDate, varchar(10), 101)
    I don't suppose I can convince you to put this into a stored procedure to call from the VB.NET application so you don't have lots of concatenated SQL strings running around in your code?

  3. #3
    Join Date
    Jul 2003
    Posts
    59
    Provided Answers: 1
    Quote Originally Posted by MCrowley View Post
    Well, I did not know about the FORMAT function in SQL 2012. Might be nice to remember, although most formatting should really be done by the application layer that is closest to the user (with the database being firmly "in the rear with the gear"). You will want to use the CONVERT function as you already found out, the syntax should be replacing
    Code:
    "FORMAT(venueEventDate, 'MM/dd/yyyy', 'en-US')
    with
    Code:
    "CONVERT(venueEventDate, varchar(10), 101)
    I don't suppose I can convince you to put this into a stored procedure to call from the VB.NET application so you don't have lots of concatenated SQL strings running around in your code?
    EERK! Seems even that doesnt work

    'varchar' is not a recognized built-in function name.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    This is why you should really have this as a stored procedure, rather than concatenating (OK, lecture over).

    Have the program spit out the contents of the string that holds the query. It is probably something silly like a misplaced quote, or something.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Either that, or just have my head examined.
    Code:
    "CONVERT(varchar(10), venueEventDate, 101)
    (I had the arguments in the wrong order.)

  6. #6
    Join Date
    Jul 2003
    Posts
    59
    Provided Answers: 1
    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?

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    You would think, but that is usually just the first character of the select statement. You may be getting some garbage data getting fed into the RADIANS functions. This might be easier to troubleshoot if this were a stored procedure in SQL Server (what do you mean that horse is dead?). That way you could separate out the concatenation errors from the actual SQL errors.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •