SELECT latitude, longitude, SQRT( POW(69.1 * (latitude – [startlat]), 2) + POW(69.1 * ([startlng] – longitude) * COS(latitude / 57.3), 2)) AS distance FROM TableName HAVING distance < 25 ORDER BY distance; where [starlat] and [startlng] is the position where to start measuring the distance.
To change the default character set and collation of a table including those of existing columns (note the convert to clause): alter table <some_table> convert to character set utf8mb4 collate utf8mb4_unicode_ci; Edited the answer, thanks to the prompting of some comments: Should avoid recommending utf8. It’s almost never what you want, and often leads to … Read more
It depends on what you want to test exactly. Information schema? To find “whether the table exists” (no matter who’s asking), querying the information schema (information_schema.tables) is incorrect, strictly speaking, because (per documentation): Only those tables and views are shown that the current user has access to (by way of being the owner or having … Read more
Try this: Remove the constraint DF_Movies_Rating__48CFD27E before changing your field type. The constraint is typically created automatically by the DBMS (SQL Server). To see the constraint associated with the table, expand the table attributes in Object explorer, followed by the category Constraints as shown below: You must remove the constraint before changing the field type.
RANK() gives you the ranking within your ordered partition. Ties are assigned the same rank, with the next ranking(s) skipped. So, if you have 3 items at rank 2, the next rank listed would be ranked 5. DENSE_RANK() again gives you the ranking within your ordered partition, but the ranks are consecutive. No ranks are … Read more
There are issues with leap year/days and the following method, see the update below: try this: DECLARE @dob datetime SET @dob=’1992-01-09 00:00:00′ SELECT DATEDIFF(hour,@dob,GETDATE())/8766.0 AS AgeYearsDecimal ,CONVERT(int,ROUND(DATEDIFF(hour,@dob,GETDATE())/8766.0,0)) AS AgeYearsIntRound ,DATEDIFF(hour,@dob,GETDATE())/8766 AS AgeYearsIntTrunc OUTPUT: AgeYearsDecimal AgeYearsIntRound AgeYearsIntTrunc ————————————— —————- —————- 17.767054 18 17 (1 row(s) affected) UPDATE here are some more accurate methods: BEST METHOD FOR … Read more
Generally speaking, IN and JOIN are different queries that can yield different results. SELECT a.* FROM a JOIN b ON a.col = b.col is not the same as SELECT a.* FROM a WHERE col IN ( SELECT col FROM b ) , unless b.col is unique. However, this is the synonym for the first query: … Read more
For static queries, like the one in your question, table names and column names need to be static. For dynamic queries, you should generate the full SQL dynamically, and use sp_executesql to execute it. Here is an example of a script used to compare data between the same tables of different databases: Static query: SELECT … Read more
This is simply an inefficient use of SQL, no matter how you do it. perhaps something like right(‘XXXXXXXXXXXX’+ rtrim(@str), @n) where X is your padding character and @n is the number of characters in the resulting string (assuming you need the padding because you are dealing with a fixed length). But as I said you … Read more
Compare value of phone2 with empty string: select phone, phone2 from jewishyellow.users where phone like ‘813%’ and phone2<>” Note that NULL value is interpreted as false.