BETWEEN with DATETIME field

Asked

Viewed 288 times

0

Using the BETWEEN with DATETIME database fields the same does not return the records with date equal to datai and dataf.

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

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')

Request field is DATATIME type, and records in the database recorded with TIME, e.g. 2016-05-06 15:51:35. I’m using a PDO. How to return records with search dates?

php mysql pdo

  • Your fields are out of time, that’s the first problem. Another thing is that if you are using PHP, you should already send the right date in SQL format, and not use DATE_FORMAT. Only makes sense DATE_FORMAT to save band on certain darlings (which is not the case with your code). Besides, it seems to me that the data you passed on the question does not match a real situation, because the way you did, it was to return from day 1 to day 5, and not from day 2 to day 5. Put the part in PHP also, that maybe we can fix.

1 answer

3


Since you didn’t post the PHP code, follow the initial setting of the query only:

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 arrange the date on the PHP side, and not use DATE_FORMAT anywhere in the query.

An example of format setting in PHP is this (there are several others, depends on your code):

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

The only sense that would make of formatting something on the SQL side, would be in situations where you will have a very large saving of band, as return only the month of a complete date numerically, and things like that, and even then it’s necessary to see if it makes up for the extra processing on the server.

Browser other questions tagged php mysql pdo

You are not signed in. Login or sign up in order to post.