There is no case with dates in a query

Question

There is no case with dates in a query

Navigation

#1 by (1 votes)

0

I'm fighting with mysql and don PHP because they do things that I do not understand. I have a table that has these fields:

    id         bigint(20)
    tit_eve    varchar(200)
    fec_dde    date
    fec_hta    date  
    organiz    varchar(200)

I do this query ...

SELECT 
id, 
DATE_FORMAT(fec_dde, '%d-%m-%Y') AS fec_dde, 
DATE_FORMAT(fec_hta, '%d-%m-%Y') AS fec_hta, 
tit_eve, 
organiz 
FROM eventos 
WHERE (fec_hta >= '10-07-2017') AND eve_apr = 1 ORDER BY fec_hta

returns ANYTHING !!! In PHP I assemble the following:

date_default_timezone_set("America/Buenos_Aires");
$diaActual = date("d-m-Y");
echo $diaActual;

$sql = "SELECT id, DATE_FORMAT(fec_dde, '%d-%m-%Y') AS fec_dde, DATE_FORMAT(fec_hta, '%d-%m-%Y') AS fec_hta, tit_eve, organiz FROM eventos WHERE (fec_hta >= '$diaActual') AND eve_apr = 1 ORDER BY fec_hta DESC";

echo $sql;

$resultado = $mysqli->query($sql);

Although the date I compare is today: 07-10-2017 this query brings me dates of 07-03-2017 and does not order anything ... I do not know what to do !!!

PS: PHP ignores until the order by ID !!! neither ascending nor descending ...

mysql php

asked by MNibor 11.07.2017 в 02:35

source

1 answer

Xamarin, The component 'System.ObjectModel' could not be found Error when passing variable from Javascript to PHP from the same file?

score 1 · Accepted Answer

The part of the WHERE in your query is wrong

... WHERE (fec_hta >= '10-07-2017') AND eve_apr = 1 ORDER BY fec_hta

Even if you use date_format in the fields, in the comparison where is not taken into account.

It should be this way so that it has the expected result, date like aaaa-mm-dd

... WHERE (fec_hta >= '2017-07-10') AND eve_apr = 1 ORDER BY fec_hta

Likewise if you still want to pass the date as dd-mm-yyyy, you should do a conversion in the WHERE from string to date with STR_TO_DATE , even if this will only slow down the query:

... WHERE ( fec_hta >= STR_TO_DATE('10-07-2017', '%d-%m-%Y') ) AND eve_apr = 1 ORDER BY fec_hta