CODEWITHSUNDEEP
sqlsql-server
chdzma
chdzma

Reputation: 191

SQL query does not return correct results

I am trying to filter between two dates on a SQL server from a PHP process.

This is the query:

select *
from webStocks
where FECHAMODIFICADO between '2020-06-03 17:16:02' and  '2020-06-04 17:16:03'
ORDER BY webStocks.FECHAMODIFICADO DESC

This is the result:

enter image description here

The result is not what I want. In the table I have the following information and it should be the result.

enter image description here

What am I doing wrong in the query?:(

Upvotes: 0

Views: 1126

Answers (2)

My Koryto
My Koryto

Reputation: 667

I'd try to make sure the date column actually contains 'timestamp' data type.

If it doesn't, the following code should fix it:

SELECT * FROM webStocks where CAST(FECHAMODIFICADO AS timestamp) BETWEEN '2020-06-03 17:16:02' AND '2020-06-04 17:16:03' ORDER BY webStocks.FECHAMODIFICADO DESC

You can see more information about this kind of statements here. (this solution is valid mostly for MySQL, but will probably work with either CAST or CONVERT statement with other SQL servers).

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269513

SQL tables represent unordered sets. That means that when you run a query with no ORDER BY, the results can be in any order -- and even in different orders on different runs.

In this case, you have an ORDER BY. But the column has duplicates. The same principle applies: rows with the same key value can be in any order -- and even in different orders on different runs.

So, you need to add a new key to capture the order that you want. It is not obvious from your data. But the results would at least be stable if you used:

ORDER BY webStocks.FECHAMODIFICADO DESC, CodeArticulo

It is also odd that your WHERE clause includes very specific times. But the data in these rows is all occurring at midnight. Usually midnight is not such an active time, if the time stamps represent human behavior.

Upvotes: 1

Related Questions