break date into 3 columns in a mySQL query [duplicate]

1

I have a system where I need to select any date in a calendar, so accurate returns all the records of the month of that date, for example: I have 4 dates, 08/01/2017 , 06/08/2017 , 14/09/2017, 2017 . Suppose I clicked the date "08/01/2017" and my query should return all dates recorded in the month of August. In this case the return has to be 08/01/2017 and 06/08/2017.

Follow the code of how I tried to do it.

if (isset($_POST['calendar']) AND $_SERVER['REQUEST_METHOD'] == 'POST'){

    $date = $_POST['date'];

    $cashier = listarColunas("cashier", " DATE_FORMAT(cas_day,'%d/%m/%Y') as 
    cas_day", "WHERE MONTH(cas_day) = '10'");

The cas_day field is where I store the date, but I needed to break it in 3 parts, because that did not work.

Note: This is my role

listarColunas($tabela,  $colunas, $parametros = null)
    
asked by anonymous 07.10.2017 / 01:36

2 answers

2

If I understand you, do you want to break the date into columns for easy reference per month?

Instead you can convert your date string to MySQL date using STR_TO_DATE and compare oranges with oranges in the WHERE = clause

$date = "01/08/2017"; //Pela pergunta esse deve ser o formato do $_POST['date']
$cashier = listarColunas("cashier", " DATE_FORMAT(cas_day,'%d/%m/%Y') as 
cas_day", "WHERE MONTH(cas_day) = MONTH(STR_TO_DATE('$date','%d/%m/%Y'))");

Here is an example of the query itself in SQL Fiddle: link

    
07.10.2017 / 04:08
-1

I'm considering that your $ _POST ['date'] is a string with a date

if (isset($_POST['calendar']) AND $_SERVER['REQUEST_METHOD'] == 'POST'){

$date = new DateTime($_POST['date']);
$mes = $date->format('m');

$cashier = listarColunas("cashier", "DATE_FORMAT(cas_day,'%d/%m/%Y')", "WHERE DATE_FORMAT(cas_day,"%c") = ".$mes);
    
07.10.2017 / 03:50