SELECT for date with format dd/MM/yyyy hh:mm:ss

Question

Consultation

SELECT 
campo
FROM 
Tabela
WHERE data BETWEEN '2016-10-20' AND '2016-10-20'

data and a field with type datetime ,I am having problems with the records for example that have the following value :

2016-10-20 19:00:00

Dates with this type of value do not appear in the query.

What would be the appropriate form of consultation for the format yyyy/MM/dd/ hh:mm:ss ? and the user chooses only the initial and final date in the format dd/MM/yyyy

Answer 1

Use the function Date(), which works more or less like the TRUNC() oracle.

SELECT 
campo
FROM 
Tabela
WHERE Date(data) BETWEEN '2016-10-20' AND '2016-10-20'