BETWEEN with field type DATETIME

Question

BETWEEN with field type DATETIME

Navigation

#1 by (3 votes)

0

Using BETWEEN with DATETIME database fields it does not return any records with date equal to datei and dataf.

Using DATE_FORMAT to search by date by ignoring TIME Considering the search $ dtai = '01 / 06/2016 'and $ dtaf = '06 / 05/2016', returns results only between 02/06/2016 and 05/05/2016, no data returned for the period searched, in the database there is data for the period searched.

SELECT u.Nome, u.Email, DATE_FORMAT(cp.DataSolicitacao,'%d/%m/%Y') as DataSolicitacao, cp.CodigoPromo, cp.IdTransacao
    FROM cartao_pedidos AS cp
    INNER JOIN usuarios AS u ON u.Id = cp.IdUsuario
    INNER JOIN codigospromo AS cpp ON cpp.Numero = cp.CodigoPromo
    WHERE cpp.IdColaborador = :idc AND cp.DataSolicitacao BETWEEN DATE_FORMAT($dtai,'%Y-%m-%d')  AND DATE_FORMAT($dtaf,'%Y-%m-%d')

DataTest field is type DATATIME, and records in the database are recorded with TIME, eg. 2016-05-06 15:51:35. I'm using PDO. How do I return the records to the search dates?

php mysql pdo

asked by anonymous 06.05.2016 / 23:52

1 answer

How to pass the value of a variable through a button to another class in Swift Problem starting java program with windows

score 3 · Accepted Answer

As you did not post the PHP code, the initial setting of query only follows:

SELECT     u.Nome, u.Email, cp.DataSolicitacao, cp.CodigoPromo, cp.IdTransacao
FROM       cartao_pedidos AS cp
INNER JOIN usuarios AS u ON u.Id = cp.IdUsuario
INNER JOIN codigospromo AS cpp ON cpp.Numero = cp.CodigoPromo
WHERE      cpp.IdColaborador = :idc
           AND cp.DataSolicitacao BETWEEN
                '$datacerta_inicial 00:00:00' AND '$datacerta_final 23:59:59'

Ideally you will sort the date on the PHP side, and do not use DATE_FORMAT anywhere in the query .

An example of formatting in PHP is this (there are several others, depending on your code):

$datacerta_inicial = substr($datai,6,4).'-'.substr($datai,3,2).'-'.substr($datai,0,2);

The only sense I would make of formatting something from the SQL side would be in situations where you're going to have a very large bandwidth savings, such as returning only the month of a complete date numerically, and things like that, and even then you need to see whether it compensates for extra processing on the server.